February 26, 2011 at 8:11 am
I have generic error logging SP that I call from SPs CATCH block to log the error and then ReRaise the error for the caller to catch. It works SP to SP. But the calling C# code does not get the exception.
After a lot of tests, I have realized that if one does RAISERROR in the SP code (with out TRY/CATCH), it works, but if RAISERROR is done from within a CATCH block it doesn't!
Why? Is this a SQL 2008 Bug? Any suggested workarounds?
Here are my 2 test Stored Procs to demo the problem:-
Calling spCauseError0 from C# - no exception is thrown back to the code
Calling spCauseError1 from C# works (an exception is thrown back to the code)
ALTER PROCEDURE [dbo].[spCauseError0]
AS
BEGIN
DECLARE @ErrorMessage NVARCHAR(512)='';
BEGIN TRY
SELECT 1/0 -- Cause an error
END TRY
BEGIN CATCH
RAISERROR( 'An Error has occurred in my SP!', 16, 1 );
END CATCH
END
ALTER PROCEDURE [dbo].[spCauseError1]
AS
BEGIN
RAISERROR( 'An Error has occurred in my SP!', 16, 1 );
END
February 27, 2011 at 2:08 am
Duplicate post from 2008 forum.
Answer given at http://www.sqlservercentral.com/Forums/Topic1070081-392-1.aspx:
The SELECT 1/0 returns an empty result set. Execute Scalar only opens the first result, so your code never encounters the error in the second result. Change SELECT 1/0 to DECLARE @s-2 INT; SET @s-2 = 1/0; to see the difference. Avoid returning any result sets from the procedure before calling Execute Scalar, or use Execute Reader and call Reader.NextResult to process the packet with the error inside.
Please don't cross-post please. Thank you.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply