RAISERROR from Catch Block in TSQL Passed to Calling Batch - Need that Passed to Calling Application

Question

I have been researching the TRY/CATCH block and I am a little stumped on how to pass an error the way I need to do so. From what I have read and if I understand correctly, a RAISERROR in a CATCH block in SQL will be passed to the calling batch OR the calling application. The application is running a stored procedure which has a transaction in it. The transaction is wrapped in a TRY/CATCH block. In the CATCH block, I am raising the error if something in the transaction fails causing it to jump to CATCH. If running the procedure via SSMS, the error shows up fine and the transaction rolls back. However, if the application calls the stored procedure and the same error occurs, the application never knows about the error and thus doesn't now the procedure failed.

First of all, am I understanding correctly how the RAISERROR in CATCH works? If so, how can I get that error raised back to the calling application?

BEGIN TRY 
    BEGIN TRAN 
        ............... 
    COMMIT 
END TRY 
BEGIN CATCH 
    IF @@TRANCOUNT > 0 
        ROLLBACK 
    DECLARE @ErrMsg NVARCHAR(4000) 
    SELECT @ErrMsg = ERROR_MESSAGE() 
    RAISERROR(@ErrMsg, 16, 1) 
END CATCH 

I am Running Windows 7, SQL Server 2005

Answer 1

The easiest way to do what your trying to do would be to declare @ErrMsg as an output parameter of your procedure and handle that in your calling application.

You can read up on how RAISEERROR handles it's output here if you wish to continue using it to handle your error outputs. http://msdn.microsoft.com/en-us/library/ms178592.aspx