How to Use transactions in SQL Server Stored Procedures,

Source: Internet
Author: User

How to Use transactions in SQL Server Stored Procedures,

This article shares with you how to use transactions in the SQL Server Stored Procedure. The specific code is as follows:

Create proc usp_Stock @ GoodsId int, @ Number int, @ StockPrice money, @ SupplierId int, @ EmpId int, @ StockUnit varchar (50), @ StockDate datetime, @ TotalMoney money, @ ActMoney money, @ baseId int, @ Description nvarchar (255) as declare @ error int = 0 -- operation error record in transaction -- enable transaction begin transaction -- add the incoming goods information insert into StockInfo values (@ GoodsId, @ Number, @ StockPrice, @ SupplierId, @ EmpId, @ StockUnit, @ StockDate, @ TotalMoney, @ ActMoney, DEFAULT, @ Description, @ baseId) set @ error + = @ ERROR -- records possible error numbers -- obtains the ID column of the current incoming goods information -- determines whether the current product has an incoming goods record if exists (select * from dbo. inventoryInfo where goodid = @ GoodsId) -- indicates that the record exists and you can directly modify the inventory quantity begin update dbo. inventoryInfo set GNumber = GNumber + @ Number, totalMoney + = @ TotalMoney where goodid = @ GoodsId set @ error + = @ ERROR -- Record the possible error code "end else" -- this product has never had a purchase record, then you should add a new existing information begin declare @ GWarningNum int -- number of alerts for this product -- obtain the number of alerts set @ GWarningNum = (select WaringNum from dbo. goodsInfo where GId = @ GoodsId) insert into dbo. inventoryInfo values (@ GoodsId, @ Number, @ baseId, @ GWarningNum, @ TotalMoney, 'First purchase ', default) set @ error + =error -- Record the possible ERROR number end -- determine whether the transaction is committed or rolled back if (@ error <> 0) begin rollback transaction return-1 -- set the operation result error identifier endelse begin commit transaction return 1 -- endgo

I hope this article will help you learn about database operations.

Articles you may be interested in:
  • SQL Server Stored Procedure Combined with the transaction code
  • SQL Server Functions, stored procedures, cursors, and transaction templates
  • SQL Server distributed transactions
  • SQL Server Stored Procedures return values with transaction concatenation id
  • Use Sqlserver transaction publishing for data synchronization (sql2008)
  • Detailed parsing of transactions and locks in sqlserver
  • SQLServer2005 trigger prompts other sessions to use the transaction context Solution
  • C # SQL Server transaction processing example

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.