Dear SQLers:

I wish to define some user-defined errors. For example:

execute sp_addmessage
@msgnum=50001,@severity=16,
@msgtext=N'String %s has too many lines.',@replace=N'replace'

They might have different parameter types. For example, another might
have a money amount.

In my stored procedures, I could have a number of raiserror()s
using them. How do I handle this in try-catch so that the error
number returned to the caller is not 50000 but whatever I define. I
might have a try block of:

begin try
...
raiserror(50001,16,1,N'ACUK')
...
raiserror(50002,16,1,@TranBalance)
...
end try

If I wish to rethrow the error and return all particulars to the
caller, is there any way to do it? If so, easily? I see four
possibilities, none of which do quite what I want. Is there something
that I am overlooking?

1) raiserror() with an error message

I could do:

begin catch
...
declare
@ErrMsg varchar(max),
@ErrSeverity int,
@ErrState int
select
@ErrMsg=error_message(),
@ErrSeverity=error_severity(),
@ErrState=error_state()
raiserror(@ErrMsg,@ErrSeverity,@ErrState)
end catch

but this will return 50000 as the error number. I want to return the
user-defined error number.

2) raiserror() with an error number

begin catch
...
declare
@ErrNr int,
@ErrSeverity int,
@ErrState int
select
@ErrNr=error_number(),
@ErrSeverity=error_severity(),
@ErrState=error_state()
raiserror(@ErrNr,@ErrSeverity,@ErrState)
end catch

but then I lose the substitution into the user-defined error message.
3) Somehow keep track of the raiserror() user-defined parameters and
have as many cases as necessary in the catch to handle then.

While this will do it, this is extremely verbose and prone to
error. It amounts to duplicating each raiserror() user-defined error
number call, once (or more) in the try and once in the catch.

4) Abandon use of try-catch

This works, but if I need to do some processing in the stored
procedure (not in the caller) after the error -- such as closing and
deallocating a cursor -- I am out of luck.


Is there something that I am overlooking?

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Not really, I think you have summrised the alternatives quite well.

What I can add is:

1) I don't like user-defined messages as they work today in SQL Server for
two reasons:
a) They are defined on server-level.
b) Numbers are not very mnemonic.

2) Look at http://www.sommarskog.se/error_handling_2005.html which is
a variation of your first alternative, but the re-raised message is
formatted to make it easy to parse.

3) The next version of SQL Server will have command for re-raising errors
exactly as they were given.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Fri, 13 May 2011 07:43:05 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Erland! You are not doing your part! You were supposed to tell
what I was missing. <SIGH> T-SQL has a lot of warts. I fight with
the language a lot, because it violates the Law of Least Astonishment
so much. (My most recent one is that raiserror()'s string error
message can not be a string expression.)

I am experimenting with something that could be returned to the
UI level.

I wish that there was something like #include and #define so I
could define mnemonics for the errors I wish to define. I have
looked, but I did not find anything.

I might go that way. Your page is a good read.

That is good in general, but I have a gotcha. <sigh> I will not
be able to use it for the company that the main app is for. They will
stick with Windows XP for quite some time. SS 2011 will not run on
XP.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
As a matter of fact, I did! I told you were missing the most recent
version of SQL Server, if only in beta. :-)

Sometimes I think that maximising the astonishment has been a prime
design goal of the language.

It is not available in the product as such, but you should be able
to roll your own with help of the preprocessor from C++ if you have
Visual Studio.

As a matter of fact, in the system I spend most of my time with, we
do use a preprocessor. Not the one from C++, but a homebrew. It's
part of my toolset, AbaPerls, which you can find at
http://www.sommarskog.se/AbaPerls/index.html. But you would not
start using AbaPerls only to get a preprocessor.

What can I say... The 64-bit support for XP is not very good, and
there are a few things missing. But not much. And I much rather
do my daily chores on XP than on Vista or Win7.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx