This article has shared the use of transactions in SQL Server stored procedures, as shown in the following code
create proc usp_stock @GoodsId int, @Number int, @StockPrice money, @SupplierId int, @EmpId int, @StockUnit varchar ( @StockDate datetime, @TotalMoney money, @ActMoney money, @baseId int, @Description nvarchar (255) as DECLARE @ Error int = 0--Errors recorded for operations in transactions--open transaction BEGIN TRANSACTION--add insert into stockinfo values (@GoodsId, @Number, @StockPrice, @SupplierId, @EmpId, @StockUnit, @StockDate, @TotalMoney, @ActMoney, DEFAULT, @Description, @baseId) SET @ error+=@ @ERROR--Record the possible error number--Get the identity column of the current purchase information--to determine if the current item has a stock record if exists (SELECT * FROM dbo. Inventoryinfo where goodid= @GoodsId)--Indicates that the record exists, directly modifies the inventory quantity to begin the update dbo. Inventoryinfo set gnumber=gnumber+ @Number, totalmoney+= @TotalMoney where goodid= @GoodsId set @error +=@ @ERROR-records are available
Can produce the error number end ELSE--this product never had a stock record, then you should add new presence information begin declare @GWarningNum INT--The number of alerts for this item --Gets 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--records are available can generate error number end--to determine the commit of a transaction or rollback if (@error <>0) begin ROLLBACK TRANSACTION return-1--set operation result error identification en
D else begin COMMIT TRANSACTION return 1--The successful identity end of the operation
Hopefully this article will help you learn about database operations.