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?
IF @@TRANCOUNT > 0
DECLARE @ErrMsg NVARCHAR(4000)
SELECT @ErrMsg = ERROR_MESSAGE()
RAISERROR(@ErrMsg, 16, 1)
I am Running Windows 7, SQL Server 2005
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