How to Return an Error in a Stored Procedure and Insert into a Table

I don't know that much about SQL Server but essentially what I have here is a stored procedure that should insert parameters (id (primary key) and Test data) into a test table, dbo.test

If it fails to add them it should instead add to dbo.Error_Log with the id, test data AND Error information.

My stored procedure looks like this:

    @Id int = 0, 
    @Test_column nvarchar(10) = 0
AS
BEGIN

    SET NOCOUNT ON;


    IF @@ERROR <> 0
        BEGIN
        INSERT INTO AdventureWorks2012.dbo.Error_Log ([Id], [Data], [Error_description])
        SELECT  @Id,
                @Test_column,
                @@ERROR
        END
        ELSE
    INSERT INTO AdventureWorks2012.dbo.Test ([Id], [Test_column])
    SELECT  @Id, 
            @Test_column
END

And I'm executing it like this:

USE [AdventureWorks2012]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[uspTester]
        @Id = 1,
        @Test_column = N'data123'

SELECT  'Return Value' = @return_value

GO


SELECT * FROM AdventureWorks2012.dbo.Test
SELECT * FROM AdventureWorks2012.dbo.Error_Log

It returns a populated dbo.Test, an empty dbo.Error_Log and a return value of -4 for some reason

Error is:

Msg 2627, Level 14, State 1, Procedure uspTester, Line 21 Violation of PRIMARY KEY constraint 'PK__Test__3214EC0775996C4D'. Cannot insert duplicate key in object 'dbo.Test'. The duplicate key value is (1). The statement has been terminated.

I need to have that error output into dbo.Error_Log and not just stop the operation completely.

2

1 Answer

The @@ERROR variable "returns the error number for the last Transact-SQL statement executed" i.e. you need to check the @@ERROR after you attempt the insert. Something like this:

BEGIN

SET NOCOUNT ON;


INSERT INTO AdventureWorks2012.dbo.Test ([Id], [Test_column])
SELECT  @Id, 
        @Test_column

IF @@ERROR <> 0
    BEGIN
    INSERT INTO AdventureWorks2012.dbo.Error_Log ([Id], [Data], [Error_description])
    SELECT  @Id,
            @Test_column,
            @@ERROR
    END
END

Note: If you want the error message instead of the number you are going to have make use of the ERROR_MESSAGE() function:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

James H. Sterling

James H. Sterling

Environmental Science & Climate Journalist

James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.

Share this article
Twitter Facebook Pinterest