CODEWITHSUNDEEP
sql-server-2008stored-procedureserror-handling
thevan
thevan

Reputation: 10344

How to capture error in SQL Server stored procedure?

I need to capture the error and send it to the web application. For example, the below is my stored procedure:

CREATE PROC ADDNAME
   (@FirstName VARCHAR(10),
    @LastName VARCHAR(10))
AS
BEGIN
    BEGIN TRY
    BEGIN TRAN
       INSERT INTO EMPLOYEE(FirstName, LastName) 
       VALUES (@FirstName, @LastName)
    END TRY
    BEGIN CATCH
       ROLLBACK TRAN
       PRINT @@ERRORMESSAGE
    END CATCH
END

Here, how to capture the error and send it to the web application? Normally, which is the best method to handle stored procedure errors from web application?

I am using ASP.NET & C# as a front end. I need your suggestions.

Upvotes: 1

Views: 4863

Answers (2)

Andamon A. Abilar
Andamon A. Abilar

Reputation: 171

Please try this: Code in SQL

create procedure sp_err_msg
as begin
    -- RAISERROR with severity 11-19 will cause execution to 
    -- jump to the CATCH block.
    -- better place this inside catch block
    -- RAISERROR ('Error raised in TRY block.', -- Message text.
    --           11, -- Severity.
    --           1 -- State.
    --       );
    raiserror('error message', 11, 1);
end

Code in C#. Just to show how to get the error from SQL

public static string GetErrorMessage() 
{
  string errMsg = null;
  try
  {
    using (var con = new SqlConnection(connectionString)) 
    {
      using (var cmd = new SqlCommand("sp_err_msg", con)) 
      {
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();

        cmd.ExecuteNonQuery();
      }
      con.Close();
    } 
  }
  catch(Exception e) {
    errMsg = e.Message;
  }

  return errMsg;
}

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294227

First, you cannot rollback in BEGIN CATCH. Not without checking XACT_STATE() first. Unless the xact_state() is 1, you cannot run ROLLBACK. Think about the trivial case when the exception is 1205 (a deadlock has occurred) in which case you get the exception after your transaction was forcefully rolled back. See Exception handling and nested transactions for a correct pattern that mixes transactions and error handling.

Second, your stored procedure does not need transaction and error handling as is. Unless the real code is much more complex, your procedure doesn't add any value.

Lastly, to raise an error use RAISERROR(). SQL Server 2012 has also THROW.

Upvotes: 4

Related Questions