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)...
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.
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: