• Transactions in SQL Server Strored Procedures

    Posted on July 17, 2012 by in Database, SQL Server

    Transactions are often used within stored procedures to guarantee that all steps succeed or all steps fail. For Example, if you want to insert data into header table and line tables, ideally you would want the data to be inserted into both header and line tables or you do not want both of the inserts to fail.

    The Problem

    Assume, you have two tables JRNL_HEADER and JRNL_LN, and you have a stored procedure as below to insert data into both the tables.

    CREATE PROCEDURE [dbo].[spManageJournal]
    (
        @JRNL_ID VARCHAR(10),@JRNL_DT DATETIME,@LINE_NO INT,@AMOUNT DECIMAL(10,2)
    )
    AS
    BEGIN
    
    --INSERT DATA INTO JRNL HEADER
    INSERT INTO JRNL_HEADER (JRNL_ID ,JRNL_DT ) VALUES (@JRNL_ID,@JRNL_DT)
    
    --INSERT DATA INTO JRNL LINE
    INSERT INTO JRNL_LN (JRNL_ID ,JRNL_DT , LINENO, AMOUNT ) VALUES (@JRNL_ID,@JRNL_DT,@AMOUNT,@LINE_NO)
    
    END
    

    If you try to execute the above store procedure, the data gets inserted into JRNL_HEADER but not into JRNL_LN since the @AMOUNT and @LINE_NO have been interchanged. If we want both the statements to execute or both of them to fail we can use TRANSACTIONS.

    CREATE PROCEDURE [dbo].[spManageJournal]
    (
        @JRNL_ID VARCHAR(10),@JRNL_DT DATETIME,@LINE_NO INT,@AMOUNT DECIMAL(10,2)
    )
    AS
    BEGIN
    
        BEGIN TRANSACTION
            --INSERT DATA INTO JRNL HEADER
            INSERT INTO JRNL_HEADER (JRNL_ID ,JRNL_DT ) VALUES (@JRNL_ID,@JRNL_DT)
    
        -- Rollback the transaction if there were any errors
        IF @@ERROR <> 0
        BEGIN
            -- Rollback the transaction
            ROLLBACK
    
    	-- Raise an error and return
    	RAISERROR ('Error inserting data into JRNL HEADER.', 16, 1)
    	RETURN
        END
            
            --INSERT DATA INTO JRNL LINE
            INSERT INTO JRNL_LN (JRNL_ID ,JRNL_DT , LINENO, AMOUNT ) VALUES (@JRNL_ID,@JRNL_DT,@AMOUNT,@LINE_NO)
    
        -- Rollback the transaction if there were any errors
        IF @@ERROR <> 0
        BEGIN
            -- Rollback the transaction
            ROLLBACK
    
    	-- Raise an error and return
    	RAISERROR ('Error inserting data into JRNL LINE.', 16, 1)
    	RETURN
        END
    
       COMMIT
    END
    

    In the above stored procedure, We start a transaction and only commit the transaction if the all the statements are executed without an error. If there is a error in any of the statements we ROLLBACK the transactions causing it to fail both the insert statements.

    Be Sociable, Share!

    Written by

    Vanamali Juvvadi is a Web enthusiast and loves all things design and technology. Founded qnownow with a group of friends to share anything/everything they know/find on the internet.

    View all articles by

    Email : [email protected]

    Leave a Reply