RAISERROR all the time?
I have a manager that is asking us to put the following code after each statement in our stored procs(DB is Sybase);
IF @@error != 0
select @returnCd = 250xx
RAISERROR @returnCd "Error xxx "
The question is, is this needed for everything? id think if there was some kind of error doing a select/insert/delete an error would get thrown without me checking the error code. Shouldnt RaiseError be used for capturing errors we know not to be true? like getting no results where results are expected? Is this a common practice?
Your manager is wasting your time. You are right that 'real' errors will drop out before it even gets to your @@error test. Very easy to prove this - for example write a procedure that inserts into a table, ignoring a not-null column.
create table xx_test_table (
id int not null,
val1 char(10) not null)
create proc xx_test
insert into xx_test_table ( id )
if @@error <> 0
raiserror 20000 'nonsense'
You should use raiserror for generating errors out of business exceptions that you specify, exactly as in 'no rows returned', 'wrong trade type' etc., so that calling programs/procedures can detect your return code -1 and catch the error message. Also, I wouldn't bother with clever numbers in raiserror, just use 20000 every time, and a (unique & meaningful) message. I wouldn't waste my time maintaining/interpreting error codes.
My qualifications? Sybase almost exclusively since 1989. Regards.
Managing a list of distinct error codes (master..sp_addmessage, I think) has its uses, particularly if you're using a client library that can access the errors collection after calling the procedure. The .NET SqlCommand/SqlConnection package does this nicely. Just catch SqlException, then check ex.errors.number or what have you. Sorta handy if you need to tailor your front end to gracefully bail out.
Plus you can use printf-like patterns in the error message, like "Cannot find uid %d" and pass them in at runtime. Makes isqlw testing and debugging sessions a little more sane.
Re: RAISERROR all the time? IMPORTANT CHANGE OF MIND!!
OK I want to revoke part of what I said above. The example I quoted is true, but it is a bad example. If, for the above table with 2 non-null columns, you do :
insert into xx_test_table ( id ) select 1
then that will bomb straight out of your procedure, so no error checking is meaningful. If you do
insert into xx_test_table(id, val1) select 1, null
then that is in effect the same, but IN THIS CASE, SYBASE WILL GENERATE AN ERROR MESSAGE, SET @@error NON-ZERO AND CONTINUE TO SUBSEQUENT STATEMENTS. i.e. you should then test @@error and return if not zero. Still no need to do raiserror though. This is true for any constraint/consistency type error.
Apologies for previous inaccuracy.