Dec 30, 2013

sql transaction rollback

BEGIN TRY
    BEGIN TRANSACTION 
        exec( @sqlHeader)
        exec(@sqlTotals)
        exec(@sqlLine)
    COMMIT
END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH
 
 
The BEGIN TRANSACTION and COMMIT I believe you are already familiar with. The BEGIN TRY and BEGIN CATCH blocks are basically there to catch and handle any errors that occur. If any of your EXEC statements raise an error, the code execution will jump to the CATCH block.
Your existing SQL building code should be outside the transaction (above) as you always want to keep your transactions as short as possible.
 

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib