How to get sql error in stored procedure

Question

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?

Answer 1

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.