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