By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,362 Members | 1,354 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,362 IT Pros & Developers. It's quick & easy.

Error Message

P: 55
I have read many error message articles on the web but still cannot get this to work. I need to return the description of the error that is produced to a output variable (@ErrMsg).

In my stored procedure, I assign @SQLCode to @@Error. @SQLCode is also an output variable. I got the @SQLCode to return no problem, just the description is wrong.

Expand|Select|Wrap|Line Numbers
  1. IF @SQLCode <> 0 
  2. BEGIN 
  3.    SELECT description = @ErrMsg
  4.    FROM master.dbo.sysmessages 
  5.    WHERE error = @SQLCode 
  6. END
Can anyone shed some light on this? I have heard I have to assign the error information to another table and then pull the info from that table, but I don't know how to do that either. Help is greatly appreciated.

Thanks
Mar 24 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
I have read many error message articles on the web but still cannot get this to work. I need to return the description of the error that is produced to a output variable (@ErrMsg).

In my stored procedure, I assign @SQLCode to @@Error. @SQLCode is also an output variable. I got the @SQLCode to return no problem, just the description is wrong.

Expand|Select|Wrap|Line Numbers
  1. IF @SQLCode <> 0 
  2. BEGIN 
  3.    SELECT description = @ErrMsg
  4.    FROM master.dbo.sysmessages 
  5.    WHERE error = @SQLCode 
  6. END
Can anyone shed some light on this? I have heard I have to assign the error information to another table and then pull the info from that table, but I don't know how to do that either. Help is greatly appreciated.

Thanks
Try these functions

Expand|Select|Wrap|Line Numbers
  1.  
  2.         ERROR_NUMBER() AS ErrorNumber,
  3.         ERROR_SEVERITY() AS ErrorSeverity,
  4.         ERROR_STATE() AS ErrorState,
  5.         ERROR_PROCEDURE() AS ErrorProcedure,
  6.         ERROR_LINE() AS ErrorLine,
  7.         ERROR_MESSAGE() AS ErrorMessage
-- CK
Mar 24 '08 #2

P: 55
Try these functions

Expand|Select|Wrap|Line Numbers
  1.  
  2.         ERROR_NUMBER() AS ErrorNumber,
  3.         ERROR_SEVERITY() AS ErrorSeverity,
  4.         ERROR_STATE() AS ErrorState,
  5.         ERROR_PROCEDURE() AS ErrorProcedure,
  6.         ERROR_LINE() AS ErrorLine,
  7.         ERROR_MESSAGE() AS ErrorMessage
-- CK
I have been wrapped up in meetings, hopefully I can test it this evening and I will report back then. I have another question. How would I execute this stored procedure to make sure that it is returning the error message and error code before I send it over to the application team. Thanks!
Mar 25 '08 #3

ck9663
Expert 2.5K+
P: 2,878
I have been wrapped up in meetings, hopefully I can test it this evening and I will report back then. I have another question. How would I execute this stored procedure to make sure that it is returning the error message and error code before I send it over to the application team. Thanks!

That's not a stored proc. If you're using a sql2005, you're in luck. Read more here .

-- CK
Mar 25 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.