Extended TRY-CATCH block

Share your SQL code snippets for SQL Prompt!

Moderators: David Atkinson, Anu Deshpande, Luke Jefferson, Paul Stephenson

Extended TRY-CATCH block

Postby ENedelko » Thu Feb 25, 2010 5:01 pm

Hi,

let me share this SQL block, which seems to be useful for proper error handling in stored procedures...

UPDATED: I found a defect in the original error handler (message was limited to 300 chars). Below is the refined version:

Code: Select all
BEGIN TRY
    BEGIN TRAN usp_procXXX
    $CURSOR$--
    COMMIT TRAN usp_procXXX
END TRY
BEGIN CATCH
    DECLARE
          @ErrorMessage varchar(max)
        , @ErrorSeverity int
        , @ErrorState int
    SELECT @ErrorMessage =
        'Error '
        + CAST( ERROR_NUMBER() as varchar )
        + ' "' + ERROR_MESSAGE() + '" '
        + 'raised in ' + ISNULL( ERROR_PROCEDURE(), 'raw SQL' )
        + ' line ' + CAST(  ERROR_LINE() as varchar )
        , @ErrorSeverity = ERROR_SEVERITY()
        , @ErrorState = ERROR_STATE()
    IF XACT_STATE() <> 0
    BEGIN
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION usp_procXXX;
    END
    RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState )
END CATCH
Last edited by ENedelko on Sun Mar 07, 2010 3:14 pm, edited 2 times in total.
ENedelko
 
Posts: 1
Joined: Thu Feb 25, 2010 4:53 pm

Postby aultmike » Thu Feb 25, 2010 7:57 pm

Nice! Thx for sharing!
aultmike
 
Posts: 26
Joined: Fri Jan 08, 2010 3:49 pm
Location: Canton, OH


Return to SQL Prompt code snippets

Who is online

Users browsing this forum: No registered users and 0 guests