How to Get SQL Error in Stored Procedure

How to get sql error in stored procedure

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.

Catch error message from stored procedure

The issue you are having is that the error is not occuring in the procedure, it is occuring when calling the procedure. In a simple example, create the following procedure:

IF OBJECT_ID(N'dbo.ErrorCatchTest', 'P') IS NOT NULL
    DROP PROCEDURE dbo.ErrorCatchTest;
GO
CREATE PROCEDURE dbo.ErrorCatchTest @int INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @T TABLE (I INT);
        INSERT @T (I) VALUES (5.0 / @int);

        SELECT 'OK';
    END TRY
    BEGIN CATCH
        SELECT CONCAT('Error_Message: ', ERROR_MESSAGE());
    END CATCH
END;

If I pass a valid INT to the procedure:

EXECUTE dbo.ErrorCatchTest @int = 1;

The I get OK as required.

If I a pass 0 to force an error:

EXECUTE dbo.ErrorCatchTest @int = 0;

You get the error message as required, but if I try an pass an invalid integer:

EXECUTE dbo.ErrorCatchTest @int = 'Not a number';

Then I get the error message, because the error is not within the proceudure, but while doing an implicit convert on the parameters.

The way around this is to call the procedure within a try/catch block:

BEGIN TRY
    EXECUTE dbo.ErrorCatchTest @int = 'Not a number';
END TRY
BEGIN CATCH
    SELECT CONCAT('Error_Message: ', ERROR_MESSAGE()); 
END CATCH

Return error message from stored procedure

Try to use TRY CATCH and catch your error like this:

BEGIN TRY
   delete from Test
    where  ID = @ID
END TRY
BEGIN CATCH
    SET @ErrorMessage  = ERROR_MESSAGE()
    SET @ErrorSeverity = ERROR_SEVERITY()
    SET @ErrorState    = ERROR_STATE()
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
    BREAK
END CATCH

Error Handling in Stored Procedure Try/Catch

In a single-statement stored procedure in the default auto commit mode, there is no need to start an explicit transaction or specify SET XACT_ABORT ON. Run-time errors will rollback any changes made by the statement and errors will be returned to the client without additional code.

In multi-statement procs (like the one with EXEC and MERGE in your question), an explicit transaction will ensure all-or-none behavior, allowing you to commit the transaction upon success or rollback if an error occurs. Adding structured error handling ensures the code in the TRY block doesn't continue after an error and the CATCH block provides a convenient place to centralize error handling, typically rolling back the transaction if needed and re-raising the error.

SET NOCOUNT ON suppresses DONE_IN_PROC (rowcount) messages from being returned to a client that doesn't need or expect them. This is especially important with some APIs like ADO classic (not ADO.NET) that require additional programming to handle those additional results.

SET XACT_ABORT ON ensures the transaction is rolled back after an error or client timeout occurs. When a client timeout occurs, the client API sends a cancel request to the stop the executing query so no subsequent code, including the CATCH block, is executed when SQL Server cancels the batch. SET XACT_ABORT ON will rollback the transaction immediately in this case.

Below is a structured error handling example. I didn't include calling dbo.usp_get_error_info in the catch block here because I don't know what it does. THROW will re-raise the original error.

ALTER PROCEDURE [dbo].[UspSdtSync]
AS
SET NOCOUNT ON; --suppress row count messages if not needed
SET XACT_ABORT ON; --ensure transaction is rolled back immediately after timeout

DECLARE                    @return_value INT
                          ,@RetCode INT
                          ,@RunID INT
                          ,@IntraDayID INT;

SET                        @RunID = NULL;  
SET                        @IntraDayID = NULL;

BEGIN TRAN;

EXEC                       @return_value = [DST].[SD].[STG].[API_GenerateTempView]
                           @SchemaName = N'TEST_A',
                           @ViewName = N'vw_TEST_KB_CGSE',
                           @ColumnList = N'statusE, statusF, statusG, statusH, LastModifiedDate, LastModifiedBy, LastReviewedBy, statusI, statusJ, Email, Mobile, HomePhone, WorkPhone, statusK, statusL, Dob',
                           @OrderByList = NULL,
                           @ResultSet = 1,                           
                           @RunID = @RunID,
                           @IntraDayID = @IntraDayID,
                           @RetCode = @RetCode OUTPUT;

MERGE INTO AeoiSdtTemp AS t
USING (SELECT statusE, statusF, statusG, statusH, LastModifiedDate, LastModifiedBy, LastReviewedBy, statusI, statusJ, Email, Mobile, HomePhone, WorkPhone, statusK, statusL, Dob 
FROM [DST].[SD].[TEST_KB_KTA].[vw_SDT_TEST_KB_CGSE_Temp]) AS s ON ( t.statusE = s.statusE) AND (t.statusF = s.statusF) AND (t.statusG = s.statusG) AND (t.statusH = s.statusH)

/*** Insert records directly into local KTA table ***/
WHEN NOT MATCHED THEN
INSERT (statusE, statusF, statusG, statusH, LastModifiedDate, StatusCode, LastModifiedBy, LastReviewedBy, CreatedDate, statusI, statusJ, Email, Mobile, HomePhone, WorkPhone, statusK, statusL, Dob)
VALUES(s.statusE, s.statusF, s.statusG, s.statusH, s.LastModifiedDate, '11', s.LastModifiedBy, s.LastReviewedBy, GETDATE(), s.statusI, s.statusJ, s.Email, s.Mobile, s.HomePhone, s.WorkPhone, s.statusK, s.statusL, s.Dob)

/*** Update records that exist ***/
WHEN MATCHED THEN
UPDATE SET LastModifiedDate = s.LastModifiedDate, LastModifiedBy = s.LastModifiedBy, LastReviewedBy = s.LastReviewedBy, statusI = s.statusI, statusJ = s.statusJ, Email = s.Email, Mobile = s.Mobile, HomePhone = s.HomePhone, WorkPhone = s.WorkPhone, statusK = s.statusK, statusL = s.statusL, Dob = s.Dob;

COMMIT;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK; --rollback transaction of needed
    THROW; --re-raise error to client
END CATCH;
GO

Dynamic SQL executed inside a stored procedure in a TRY/CATCH block but the error is not returned

"ERROR_PROCEDURE (Transact-SQL)":

ERROR_PROCEDURE returns NULL if the error did not occur within a stored procedure or trigger.

Since the dynamic SQL is not a procedure nor a trigger, this applies here and your @ERROR_PROCEDURE is NULL.

Now you use @ERROR_PROCEDURE in a string concatenation using + and assign that to @ERROR_MESSAGE. If you lookup the remarks in "+ (String Concatenation) (Transact-SQL)", you'll see that this can lead to the result of the concatenation being NULL and that likely happens in your case.

When you work with strings with a null value, the result of the concatenation depends on the session settings. Just like arithmetic operations that are performed on null values, when a null value is added to a known value the result is typically an unknown value, a string concatenation operation that is performed with a null value should also produce a null result. However, you can change this behavior by changing the setting of CONCAT_NULL_YIELDS_NULL for the current session.

So you pass a NULL as the first argument for RAISERROR and therefore no message is printed.

You can use concat() instead of + for the concatenation or replace NULLs manually.

But you should avoid RAISERROR anyway. See the note in "RAISERROR (Transact-SQL)".

The RAISERROR statement does not honor SET XACT_ABORT. New applications should use THROW instead of RAISERROR.

How to catch the error from a stored procedure? in sql

1st solution:

BEGIN TRY
    EXECUTE regresaerror
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Here is link to MSDN.

2nd solution:

create proc regresaerror 
(
  errmsg varchar(max) out
)
as
begin
 set errmsg =  'Error al borrar los precios especiales' 
 return 51001 -- if error
end 

declare @error varchar(max)
declare @numerror int
set @error=''   
exec @numerror = regresaerror @error out

---

Related Topics