question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

Nested Stored Procedures and Error Handling

I have nested stored procedures and I have created system defined error numbers and would like to raise an error in the nested stored procedure. However when I run this the error number raised in the parent stored procedure is 50000 and not the defined error number. Has anyone come across this and know how to handle it? Many thanks
sql-server-2008-r2error-handling
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Are you specifying an error message in the parent proc? If so then that gets an error number of 50000 From BOL: > When msg_str is specified, RAISERROR raises an error message with an error number of 50000.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is there no way round this?
0 Likes 0 ·
But I'm guessing this isn't 2012 :(
0 Likes 0 ·
Nope, SQL 2008 R2. Thanks.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Are you using a [TRY/CATCH in the outer procedure][1]? You should be able to catch the error from the inner procedure and then ensure that is what is passed back to the calling application. [1]: http://msdn.microsoft.com/en-us/library/ms179296(v=SQL.105).aspx
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ah yes, but I think @Mrs_Fatherjack is trying to re-throw the error in the catch block - in that case you always get 50000
1 Like 1 ·
Yes, using try/catch in outer and nested, but it's not returning the correct error number, it's returning 50000.
0 Likes 0 ·
This absolutely works: DROP PROCEDURE dbo.Wrapper; go CREATE PROCEDURE dbo.Wrapper AS BEGIN TRY EXEC dbo.Inner1; END TRY BEGIN CATCH SELECT ERROR_NUMBER(); END CATCH GO DROP PROCEDURE dbo.Inner1; GO CREATE PROCEDURE dbo.Inner1 AS RAISERROR (13001,15,1); GO EXEC dbo.Wrapper;
0 Likes 0 ·
Hi, we have a TRY CATCH in Inner1 so that we can pick up any other errors and when we do this the error number doesn't bubble up to the parent stored procedure properly, am I missing something?
0 Likes 0 ·
Just tested it, modifying the inner procedure. Not a 50000 error. Maybe it's different in 2012?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.