How to Use transactions in SQL Server Stored Procedures,

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