Error message in Stored procedures

How can I get the description of the sybase error in the stored procedure. For example, if I am trying to insert a record in a table and duplicate key condition occurs then how can I get the description of the error that 'Duplicate key condition occured in <table_name>' in thestore procedure itself?
AjayGAsked:
Who is Participating?
 
bretCommented:
From within TSQL, there is no way to get at the error message itself (i.e you cannot tell what the
substituted table names were).  An Open Client client can parse the error message string.

You may be able to figure out what the substituted information was from your context, i.e. if you
do an insert into mytable and get this error, well, you know which table you were just inserting into.

There are a couple  long-open feature requesst at Sybase, CRs 58371 and 31148, for this ability.
You can improve the chances they might be acted upon by promoting them through the feature request
ranking system at the International Sybase User's Group website (http://www.isug.com)

CR 31148: Wants the ability to access error message text (master..sysmessages) from a previous raise error command.  Perferably as a global variable.

CR 58371: Would like a way to get error message paramters from within TSQL.  Example: 1105 occurs, can get 1105 from @@error, but would like to get the dbname parameter.
 
-bret
0
 
amitpagarwalCommented:
hope this helps.

create proc ..

begin

declare @msg char(80)

..some sql ....

INSERT INTO ....

IF(@@ERROR != 0 )
BEGIN
SELECT @msg = "Duplicate key ..."
GOTO Error_exit
END

... some SQL ...

RETURN 0

Error_exit:

RAISERROR @msg
RETURN -1

END
0
 
amitpagarwalCommented:
You can even derive some info from this comment.

sp_addmessage 25001,"There is already a remote user named '%1!' for remote server '%2!'."

raiserror 25001, jane, myserver

This example adds a message to sysusermessages and then tests the message with raiserror, providing the substitution arguments.

Thanks,
Amit Agarwal.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AjayGAuthor Commented:
Thank you amit for your reply. But what I am looking for is how can i get the sybase's own error message corresponding to the @@error value. I do not  want to generate my own message but just print out the sybase error message corresponding to @@error value.

By your method my program will have to know what value of @@error corresponds to which error (like in the example you have given I have to know that certain value of @@error means duplicate key).
0
 
amitpagarwalCommented:
You may use master..sysmessages to find the description of the sybase error.

Thanks.
0
 
AjayGAuthor Commented:
master..sysmessages contains message templates (like you had mentioned in your earlier comment (,"duplicate key error occured while inserting record into '%1!'"). Where will I get the complete sybase message with all the substitutions done.
0
 
amitpagarwalCommented:
just thinking .. maybe sp_getmessage should solve your purpose, you can get the errror in a variable.
0
 
ahoorCommented:
I don't think it's possible, since the substitution is done in a raiserror and you can not trap it anymore then... but it's interesting.

sp_getmessage does nothing more then a select from sysusermessages, that won't work I guess...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.