[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1020
  • Last Modified:

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?
0
AjayG
Asked:
AjayG
1 Solution
 
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
 
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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
 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now