Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
380 views
in Technique[技术] by (71.8m points)

sql server - How to get sql error in stored procedure

I'm using SQL Server 2005. I created a stored procedure which works most of the time, but I found an instance of where it doesn't do what I want.

Currently, the code does something like this

if @@error <> 0
  begin
   select @message_error = "There was a database error adding product "+ @product + " to product line
  end

Where @message_error is an output variable.

So, I can select @@error and get a number, but all I really want is the SQL error.

Something like Hey, I couldn't do this because there is a fk constraint on this column or whatever. I found this article on msdn http://msdn.microsoft.com/en-us/library/ms178592(v=sql.90).aspx

But it only goes over throwing custom exceptions with RAISERROR, I don't want to create my own error message or exception, I just want to know why stuff isn't working. I can execute the stored procedure through Management Studio and see the exact SQL error, but this is tedious trying to match data from the site and manually inserting it that way.

How do I get the SQL error text into an output variable?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Here's part of a stored procedure template I use:

/*  CREATE PROCEDURE...  */

DECLARE
  @ErrorMessage   varchar(2000)
 ,@ErrorSeverity  tinyint
 ,@ErrorState     tinyint

/*  Additional code  */

BEGIN TRY

/*  Your code here  */

END TRY

BEGIN CATCH
    SET @ErrorMessage  = ERROR_MESSAGE()
    SET @ErrorSeverity = ERROR_SEVERITY()
    SET @ErrorState    = ERROR_STATE()
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

    BREAK
END CATCH

/*  Further cleanup code  */

Try/Catch blocks can be tricky but are much more thorough than @@error. More importantly, you can use the various error_xxx() functions within them. Here, I store the proper error message in variable @ErrorMessage, along with enough other data to re-raise the error. From here, any number of options are available; you could make @ErrorMessage an output variable, test for and handle specific errors, or build your own error messages (or adjust the existing ones to be clearer--you may get irritated finding out how often you'll want to do that). Other options will present themsleves.

Something to look out for: in some situations, SQL will throw two error messages back to back... and error_message() will only catch the last one, which usually says something like "attempt to create object failed", with the real error given in the first error message. This is where building your own error message comes in.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...