Red Gate forums :: View topic - Extended TRY-CATCH block
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  | Register 
Go to product support center

SQL Prompt code snippets forum

Extended TRY-CATCH block

 Search in SQL Prompt code snippets forum
Post new topic   Reply to topic
Jump to:  
Author Message
ENedelko



Joined: 25 Feb 2010
Posts: 1
PostPosted: Thu Feb 25, 2010 5:01 pm    Post subject: Extended TRY-CATCH block Reply with quote
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:
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
Back to top
View user's profile Send private message
aultmike



Joined: 08 Jan 2010
Posts: 25
Location: Canton, OH
PostPosted: Thu Feb 25, 2010 7:57 pm    Post subject: Reply with quote
Nice! Thx for sharing!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group