SQL Server Stored Procedure Try Catch and ADO.Net exception handli

[Guest]

Hi

We have recently moved onto SQL 2005 and our DBA wants us to use Try Catch
within our stored procedures.

If I have an SP such as:

CREATE PROCEDURE NoTry
AS
BEGIN

INSERT INTO MyTable(Id, Description)
VALUES (3, 'Toodles');

END

and I have a primary key on the Id field and record 3 already exists. When
I call it from ADO.Net it will result in a SQLException being thrown with the
approrpiate Constraint Violation details shown.

Now if I wrap it in a Try Catch block like

CREATE PROCEDURE TryCatch
AS
BEGIN

BEGIN TRY
INSERT INTO MyTable(Id, Description)
VALUES (3, 'Toodles');
END TRY
BEGIN CATCH
-- Log the error / rollback transaction etc.
END CATCH
END

As you would expect the exception is now not raised to ADO.Net as it is
handled by the SP.

My question is if I need to re-raise the error how do I do it or
alternatively how do I signal to ADO.Net that an error has occurred.

My first thought was to use RAISEERROR in the CATCH block however this
results in a 50000 error not the original error. Based on this my current
approach is concatinating all the error information in the message string.
Then have code in my .Net Catch block checking if it is a 50000 error and
unpacking the exception.

An alternative was to simply set the retun value to the error number.

My final thought was to ignore TRY CATCH functionality in SQL totally
however that seems extreme and self-defeating.

Have I missed something and it is possible to Reraise a SQL error?
Has anybody got any best practices on the best approach to take?

Thanks in advance

Phil

 

[WenYuan Wang [MSFT]]

Hi Phil,

As far as I know, RAISERROR shoud support this senario. It could return the
original error.
Have you tried to add the following statement in catch block?

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return
-- error information about the original error that
-- caused execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

In your case, it should be:
CREATE PROCEDURE TryCatch
AS
BEGIN
BEGIN TRY
INSERT INTO MyTable(Id, Description) VALUES (3, 'Toodles');
END TRY
BEGIN CATCH
-- Log the error / rollback transaction etc.
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return
-- error information about the original error that
-- caused execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END

Hope this helps. Please let me know if you meet any further issue on this.
I'm glad to assist you.
Have a great day.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi Wen

Thanks for your reply.

Yes, you are right that you can use RAISEERROR as you described however that
causes the problem I described in that the Error Number in the SqlException
is 50000 and not the actual error number.

Therefore in my C# catch code the only way to diagnose what the SQL problem
is would be to parse the error message which is not an ideal solution.

That is why I suggested packing all the error details including the original
Error Number into the error message.

Regards

Phil

 

[WenYuan Wang [MSFT]]

Hi, Phil
Thanks for your reply.
I'm sorry I didn't understand your issue clear in my first reply.

RAISERROR ( { msg_id | msg_str | @local_variable } { ,severity ,state }
[ ,argument [ ,...n ] ] ) [ WITH option [ ,...n ] ]
When msg_str is specified, RAISERROR raises an error message with an error
number of 50000. Thus, Error Number in the SqlException is 50000 but not
the actual error number.

ERROR_NUMBER() Returns the error number of the error that caused the CATCH
block of a TRY ... CATCH construct to be run. You can use it to rasie the
original error number for SQLException.

DECLARE @ErrorNumber INT;
....
SELECT @ErrorNumber = ERROR_NUMBER(),
....
RAISERROR (@ErrorNumber, -- ERROR NUMBER.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);

Hope this helps. Please don't hesitate to let me know if you still have any
more concern on this. I'm glad to assist you.
Have a great day,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi Wen

Thanks again for your reply. Unfortunately I don't think we're there yet.

The problem with your answer is that if you try to use RAISERROR with an
error number < 13000 this itself is not allowed, therefore this approach does
not work for the majority of caught errors as they are Microsoft errors and
have error numbers < 13000. For example a primary key violation has an error
number of 2627.

Regards

Phil

 

[WenYuan Wang [MSFT]]

Hi Phil,
Thanks for your reply.

Oops. I'm sorry it seems RAISERROR is not a good solution in this scenario.
As you see, this approach does not work for the majority of caught errors
as the error number (2627) less than 13000. However, you may check the
error exception in.Net Catch block, checking if it is a 50000 error and
unpacking the exception with RegularExpressions for the error number.
if (SqlException.Number == 50000)
{
string errorNumber =
System.Text.RegularExpressions.Regex.Match(SqlException.Message, @"Error
(\d+)").Groups[1].Value;
}
Besides that, setting the return value to the error number seems like the
simplest way.

Hope this helps. Please let me know if there is anything we can help with.
I'm glad to work with you.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);

-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();

-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
@ErrorState,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);

 

[Guest]

Hi Wen

Thanks for your reply, apologies for not replying earlier.

From your reply I guess I am back to my original approach which is to put
all the error details in the message then extract it out when I handle the
SqlException.

Regards

Phil

 

[WenYuan Wang [MSFT]]

Hi Phil
Thanks for your reply.

As you see, you may use your original approach or set the return value to
the error number.
Please let me know if you have concern on this. I'm glad to assist you.

Sincerely,
Wen Yuan
Microsoft Online Community Support

 

[Simon]

Hi Phil,

I'm looking into a similar thing as you, as I want to be able to re-
raise the error message back up from my C.R.U.D. sprocs to be handled
by the .NET TableAdapters. I may have found another solution if you
are interested, as the return value isn't much of an option for me
because of the tableadapters, as I'd have to create partial classes
for each one to get access to the return value!!?

Anyway, my idea is this:

As the reserved error messages are restricted to < 50,000, why not
just add an arbitrary offset to the actual error number (such as
100,000) and re-raise it as a custom error message. You should then be
able to get to the actual Error Number in .NET by just subtracting
100,000 from it.

To do this you do need to temporarily create it as an ad-hoc message
in sys.messages, but you can delete it immediately afterwards.

Additionally, to simplify matters it seems you can wrap up all the
error handling in an sproc and then call it in your CATCH block to
automatically raise the error appropriately. Here is the one I just
knocked up:

CREATE PROCEDURE usp_RaiseError
AS
BEGIN

DECLARE @ErrMsg nvarchar(4000),
@ErrNo int,
@ErrSev int,
@ErrState int,
@ErrLine int,
@ErrProc nvarchar(200),
@CustomErrNo int

SELECT @ErrNo = ERROR_NUMBER(),
@ErrSev = ERROR_SEVERITY(),
@ErrState = ERROR_STATE(),
@ErrLine = ERROR_LINE(),
@ErrProc = ISNULL(ERROR_PROCEDURE(), '-'),
@ErrMsg = N'Error %d, Level %d, State %d, Procedure %s, Line %d,
Message: '+ ERROR_MESSAGE(),
@CustomErrNo = 100000 + ERROR_NUMBER() -- Offset Error Number by
100,000

EXEC sp_addmessage @msgnum = @CustomErrNo,
@severity = @ErrSev,
@msgtext = @ErrMsg

RAISERROR (@CustomErrNo, @ErrSev, @ErrState, @ErrNo, @ErrSev,
@ErrState, @ErrProc, @ErrLine)

EXEC sp_dropmessage @msgnum = @CustomErrNo

END


First attempts seem to be successful, but I've not had much chance to
test it all out, so please (Phil, Wen, or anybody else for that
matter) let me know what you think and/or if you can spot any
potential drawbacks with this.

Regards,

Simon