Extended TRY-CATCH block

ENedelko

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:

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

aultmike

Nice! Thx for sharing!