如何在存储过程中编写回滚和提交事务 [英] How to write Rollback and Commit Transaction in Stored Procedure
本文介绍了如何在存储过程中编写回滚和提交事务的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
USE [ggg]
GO
/ * *****对象:StoredProcedure [dbo]。[Sp_InvDOItem]脚本日期:02/15 / 2013 15:45:14 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo]。[Sp_InvDOItem]
(
@InvDOItemId int = null,
@ IVDOno varchar ( 100 )= null,
@ Date datetime = null,
@ ProductValue decimal ( 18 , 2 )= null,
@ Tax decimal ( 18 , 2 )= null,
@ GeneratedBy int = null,
@ AssignedTo int = null,
@ BookStockId INT = null,
@ Status varchar ( 100 )= null,
< span class =code-sdkkeyword> @ InvoiceMode varchar ( 20 )= null,
@ Mode varchar ( 100 ),
@ DetailsInvDOItemId int = null,
@ ProductId int = null,
@ Quantity decimal ( 18 , 2 )= null,
@ Quantity1 十进制( 18 , 2 )= null,
@ BatchNo varchar ( 100 )= null,
@ BatchDate datetime = null,
@ Denom_Value 十进制( 18 , 2 )= null,
@ Denom_Value1 decimal ( 18 , 2 )= null,
@ BatchItemDetailsId int = null,
@ PartnerId int = null,
@ Mode1 varchar ( 250 )= null
)
as
开始
如果(@ Mode = ' < span class =code-string> INSERT')
BEGIN
INSERT INTO Tbl_InvDOItem(IVDOno,日期,ProductValue,Tax,GeneratedBy,AssignedTo,Status,InvoiceMode ,IsActive)值( @ IVDOno , @ Date , @ ProductValue , @ Tax , @ GeneratedBy , @AssignedTo , @ Status , @ InvoiceMode ,' False')
SELECT IDENT_CURRENT(' Tbl_InvDOItem')
END
if (@ Mode = ' INSERTITEM')
BEGIN
< span class =code-keyword> INSERT INTO Tbl_DetailedInvDOItem(IvDonoId,ProductId,Quantity) values ( @ InvDOItemId , @ ProductId , @ Quantity )
SELECT I DENT_CURRENT(' Tbl_DetailedInvDOItem')
END
IF (@ Mode1 = ' INSERTSTOCKBOOK')
BEGIN
INSERT INTO Tbl_StkBookStock(PartnerId,ProductId,Value) VALUES ( @ PartnerId , @ ProductId , @ Quantity1 )
END
IF (@ Mode1 = ' UPDATESTOCKBOOK')
BEGIN
更新 Tbl_StkBookStock SET 值= @ Quantity1 WHERE ProductId = @ ProductId AND PartnerId = @ PartnerId
结束
IF (@ Mode = ' INSERTBATCH')
BEGIN
INSERT INTO Tbl_BatchItemDetail(ItemDetailsId,BatchNo,BatchDate,Denom_Value,Quantity) values ( @ DetailsInvDOItemId , @ BatchNo , @ BatchDate , @ Denom_Value , @ Quantity )
END
if (@ Mode = ' UPDATE')
BEGIN
更新 Tbl_InvDOItem SET IVDOno = @ IVDOno,Date = @ Date,ProductValue = @ ProductValue,Tax = @ Tax ,GeneratedBy = @ GeneratedBy,AssignedTo = @ AssignedTo,Status = @ Status,InvoiceMode = @ InvoiceMode WHERE Itemdetailsid = @ InvDOItemId
结束
如果(@ Mode = ' UPDATEDO')
BEGIN
UPDATE Tbl_InvDOItem SET IVDOno = @ IVDOno,Date = @ Date,ProductValue = @ ProductValue,Status = @ Status,InvoiceMode = @ InvoiceMode WHERE Itemdetailsid = @InvDOItemId
END
if (@ Mode = ' ExitProductUPDATEITEM')
BEGI N
更新 Tbl_DetailedInvDOItem SET 数量= @数量 WHERE IvDonoId = @InvDOItemId 和 ProductId = @ ProductId
SELECT IDENT_CURRENT(' Tbl_DetailedInvDOItem')
结束
如果(@ Mode = ' UPDATEUPDATEITEM')
BEGIN
UPDATE Tbl_DetailedInvDOItem SET IvDonoId = @ InvDOItemId,ProductId = @ ProductId,Quantity = @ Quantity WHERE DIVitemId = @ DetailsInvDOItemId
END
if (@ Mode = 跨度> UPDATEBATCH' )
BEGIN
更新 Tbl_BatchItemDetail SET ItemDetailsId = @ DetailsInvDOItemId,BatchNo = @ BatchNo,BatchDate = @ BatchDate,Denom_Value = @ Denom_Value,Quantity = @ Quantity WHERE Id = @ BatchItemDetailsId
END
IF (@ Mode1 = ' insertbookstock')
开始
INSERT INTO Tbl_StkDetailedBookStock(BookStockId,Batchno,日期,DenomValue,数量) VALUES ( @ BookStockId ,< span class =code-sdkkeyword> @ Batchno , @ BatchDate , @ D enom_Value , @ Quantity )
end
IF (@ Mode1 = ' UPDATEDETAILEDBOOKSTOCK' )
BEGIN
更新 Tbl_StkDetailedBookStock SET DenomValue = @ Denom_Value1,Quantity = @ Quantity1 WHERE BookStockId = @ BookStockId 和 BatchNo = @BatchNo 和日期= @ BatchDate
END
end
嗨这是我存储的程序....任何人都告诉如何写回滚和提交事务,因为我想在多个表中保存数据。
解决方案
格式如下......
< pre lang =SQL> ALTER PROCEDURE [dbo]。[Insert_Mast]
- - 参数
@错误 varchar (Max)输出
AS
BEGIN
开始 交易 t1
开始尝试
将 插入 LedgMast
(
- 列列表
)
值
(
- 价值表
)
set @ LedgId = Scope_Identity()
返回
结束尝试
开始 Catch
set @ Error = Error_Message()
rollback transaction t1
return
结束 Catch
commit transaction t1
返回;
END
快乐编码!
:)
@@ Error - 返回上次执行的SQL语句的错误号。
开始事务 - BEGIN TRANSACTION表示连接引用的数据在逻辑上和物理上一致的点。如果遇到错误,可以回滚在BEGIN TRANSACTION之后进行的所有数据修改,以将数据返回到此已知的一致状态。
您可以这样写:
开始 交易
- 您的插入/更新声明在这里
如果( @@ Error <> 0 ) - 检查是否任何错误
开始
rollback 交易
结束
其他
commit transaction
USE [ggg]
GO
/****** Object: StoredProcedure [dbo].[Sp_InvDOItem] Script Date: 02/15/2013 15:45:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Sp_InvDOItem]
(
@InvDOItemId int=null,
@IVDOno varchar(100)=null,
@Date datetime=null,
@ProductValue decimal(18,2)=null,
@Tax decimal(18,2)=null,
@GeneratedBy int =null,
@AssignedTo int=null,
@BookStockId INT =null,
@Status varchar(100)=null,
@InvoiceMode varchar(20)=null,
@Mode varchar(100),
@DetailsInvDOItemId int=null,
@ProductId int=null,
@Quantity decimal(18,2)=null,
@Quantity1 decimal(18,2)=null,
@BatchNo varchar(100)=null,
@BatchDate datetime=null,
@Denom_Value decimal(18,2)=null,
@Denom_Value1 decimal(18,2)=null,
@BatchItemDetailsId int=null,
@PartnerId int=null,
@Mode1 varchar(250)=null
)
as
begin
if(@Mode='INSERT')
BEGIN
INSERT INTO Tbl_InvDOItem(IVDOno,Date,ProductValue,Tax,GeneratedBy,AssignedTo,Status,InvoiceMode,IsActive)values(@IVDOno,@Date,@ProductValue,@Tax,@GeneratedBy,@AssignedTo,@Status,@InvoiceMode,'False')
SELECT IDENT_CURRENT('Tbl_InvDOItem')
END
if(@Mode='INSERTITEM')
BEGIN
INSERT INTO Tbl_DetailedInvDOItem(IvDonoId,ProductId,Quantity)values(@InvDOItemId,@ProductId,@Quantity)
SELECT IDENT_CURRENT('Tbl_DetailedInvDOItem')
END
IF(@Mode1='INSERTSTOCKBOOK')
BEGIN
INSERT INTO Tbl_StkBookStock(PartnerId,ProductId,Value)VALUES(@PartnerId,@ProductId,@Quantity1)
END
IF(@Mode1='UPDATESTOCKBOOK')
BEGIN
UPDATE Tbl_StkBookStock SET Value=@Quantity1 WHERE ProductId=@ProductId AND PartnerId=@PartnerId
END
IF(@Mode='INSERTBATCH')
BEGIN
INSERT INTO Tbl_BatchItemDetail(ItemDetailsId,BatchNo,BatchDate,Denom_Value,Quantity)values(@DetailsInvDOItemId,@BatchNo,@BatchDate,@Denom_Value,@Quantity)
END
if(@Mode='UPDATE')
BEGIN
UPDATE Tbl_InvDOItem SET IVDOno=@IVDOno,Date=@Date,ProductValue=@ProductValue,Tax=@Tax,GeneratedBy=@GeneratedBy,AssignedTo=@AssignedTo,Status=@Status,InvoiceMode=@InvoiceMode WHERE Itemdetailsid=@InvDOItemId
END
if(@Mode='UPDATEDO')
BEGIN
UPDATE Tbl_InvDOItem SET IVDOno=@IVDOno,Date=@Date,ProductValue=@ProductValue,Status=@Status,InvoiceMode=@InvoiceMode WHERE Itemdetailsid=@InvDOItemId
END
if(@Mode='ExitProductUPDATEITEM')
BEGIN
UPDATE Tbl_DetailedInvDOItem SET Quantity=@Quantity WHERE IvDonoId=@InvDOItemId and ProductId=@ProductId
SELECT IDENT_CURRENT('Tbl_DetailedInvDOItem')
END
if(@Mode='UPDATEUPDATEITEM')
BEGIN
UPDATE Tbl_DetailedInvDOItem SET IvDonoId=@InvDOItemId,ProductId=@ProductId,Quantity=@Quantity WHERE DIVitemId=@DetailsInvDOItemId
END
if(@Mode='UPDATEBATCH')
BEGIN
UPDATE Tbl_BatchItemDetail SET ItemDetailsId=@DetailsInvDOItemId,BatchNo=@BatchNo,BatchDate=@BatchDate,Denom_Value=@Denom_Value,Quantity=@Quantity WHERE Id=@BatchItemDetailsId
END
IF(@Mode1='insertbookstock')
begin
INSERT INTO Tbl_StkDetailedBookStock(BookStockId,Batchno,Date,DenomValue,Quantity)VALUES(@BookStockId,@Batchno,@BatchDate,@Denom_Value,@Quantity)
end
IF(@Mode1='UPDATEDETAILEDBOOKSTOCK')
BEGIN
UPDATE Tbl_StkDetailedBookStock SET DenomValue=@Denom_Value1,Quantity=@Quantity1 WHERE BookStockId=@BookStockId and BatchNo=@BatchNo and Date=@BatchDate
END
end
Hi this is my stored Procedure....anybody tell how to write Rollback and Commit Transaction because i want to save data in more than one table.
解决方案
a format is below...
ALTER PROCEDURE [dbo].[Insert_Mast] --parameters @Error varchar(Max) Output AS BEGIN begin transaction t1 Begin Try Insert into LedgMast ( --column list ) values ( --value list ) set @LedgId = Scope_Identity() Return End Try Begin Catch set @Error = Error_Message() rollback transaction t1 return End Catch commit transaction t1 Return; END
Happy Coding!
:)
@@Error - Returns error number for the last SQL statement executed. Begin Transaction - BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. You can write like this :
Begin transaction -- Your Insert / Update Statement Here If (@@Error <> 0) -- Check if any error Begin rollback transaction End else commit transaction
这篇关于如何在存储过程中编写回滚和提交事务的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文