How to write Rollback and Commit Transaction in Stored Procedure

Question

0.00/5 (No votes)

See more:

SQL

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.

Posted 14-Feb-13 23:42pm

MURALIBALA1991

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Answer 1 · 2013-02-15T00:21:00

a format is below...

SQL

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!
:)

Vedangi · Answer 2 · 2013-02-15T00:09:00

@@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 :

SQL

Begin transaction      
  --  Your Insert / Update Statement Here 
  If (@@Error <> 0)   -- Check if any error
     Begin          
        rollback transaction       
     End 
   else 
       commit transaction

How to write Rollback and Commit Transaction in Stored Procedure

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0