How to retain good records when inserting from stored procedure and still catch the error message?

Question

I am trying to catch the error message when inserting records into a temp table from a stored procedure without rolling back everything. To illustrate, here is what I am trying to do:

CREATE TABLE #TEMPTABLE (some fields....)

BEGIN TRY
     INSERT INTO #TEMPTABLE
     EXEC SomeStoredProcedure @param1, @param2
END TRY
BEGIN CATCH
     ' Log error message in here
END CATCH

When executing just the INSERT INTO statement outside of a try catch block from within SSMS, I get a message similar to the following:

Msg 8115, Level 16, State 2, Procedure SomeStoredProcedure, Line 337
Arithmetic overflow error converting expression to data type int. The statement has been terminated.

BUT..., I also get some records inserted into #TEMPTABLE (perfectly fine for my requirements since I can't modify the existing SP but it still returns the data needed).

If I use the try catch block, I can capture the error message but #TEMPTABLE is empty.

Is there a way to capture the error message but still retain the records in #TEMPTABLE?

Honestly, I don't really need the try catch but this behavior got me curious.

---

Here is a sample code to reproduce the behavior I am experiencing:

-- SomeProc declaration 
CREATE PROCEDURE [dbo].[SomeProc] AS BEGIN
CREATE TABLE #TEMP(ID INT NOT NULL)

INSERT INTO #TEMP VALUES(1)
INSERT INTO #TEMP VALUES(1 / 0)

SELECT ID FROM #TEMP END
GO

The following won't insert any records on #TEMPTABLE but will return error number

CREATE TABLE #TEMPTABLE(ID INT NOT NULL)

BEGIN TRY

INSERT INTO #TEMPTABLE
EXEC dbo.SomeProc END TRY BEGIN CATCH
SELECT @@ERROR END CATCH

SELECT * FROM #TEMPTABLE

DROP TABLE #TEMPTABLE

The following will insert a record on #TEMPTABLE but will not return any error number

CREATE TABLE #TEMPTABLE(ID INT NOT NULL)

INSERT INTO #TEMPTABLE EXEC dbo.SomeProc

SELECT @@ERROR  -- returns 0

SELECT * FROM #TEMPTABLE

DROP TABLE #TEMPTABLE

Answer 1

The TRY...CATCH block is causing the execution of the stored procedure to terminate before it has a chance to return the values in #TEMP.

Try this stored proc:

IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS 
BEGIN
    CREATE TABLE #TEMP
    (
        ID INT NOT NULL
    );

    INSERT INTO #TEMP 
    VALUES (1);

    INSERT INTO #TEMP 
    VALUES (1/0);

    PRINT (N'Run the SELECT');

    SELECT ID 
    FROM #TEMP;
END
GO

Running it, like this, without a TRY...CATCH, allows all the statements in the proc to run:

EXEC dbo.SomeProc;

The output:

Msg 8134, Level 16, State 1, Procedure SomeProc, Line 12
Divide by zero error encountered.
The statement has been terminated.
Run the SELECT

However, if you run it inside a TRY...CATCH:

BEGIN TRY
    EXEC dbo.SomeProc;
END TRY
BEGIN CATCH
    PRINT (ERROR_MESSAGE());
END CATCH

You see only the error message:

Divide by zero error encountered.

The PRINT (N'Run the SELECT'); never runs, and indeed the SELECT ID FROM #TEMP; never runs either. Hence no rows are returned, and nothing can be inserted into your #TEMPTABLE

From the MSDN documentation on TRY...CATCH:

If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block.

As always, the devil is in the details. TRY...CATCH always aborts the code inside the BEGIN TRY...END TRY code block if any error over severity 10 occurs that does not close the database connection. Execution is immediately passed into the BEGIN CATCH...END CATCH block, even if this means aborting code in a stored proc.

Be aware that if an error occurs on any row the entire insert will not happen. The only reason you're seeing this error, and a row being inserted, is because you have two insert statements, one that runs to completion, and one that throws an error. Take for instance:

IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS 
BEGIN
    CREATE TABLE #TEMP
    (
        ID INT NOT NULL
    );

    INSERT INTO #TEMP 
    VALUES (1);

    INSERT INTO #TEMP 
    VALUES (1)
        , (2)
        , (3)
        , (4)
        , (1/0);

    PRINT (N'Run the SELECT');

    SELECT ID 
    FROM #TEMP;
END
GO

Using this stored proc, either inside a TRY...CATCH or with no TRY...CATCH block, will only result in a single row being inserted into the #TEMPTABLE - none of the rows in the 2nd insert will be present in the output.

Answer 2

You can get the error without catching it by using @@ERROR and then looking up the error message in sys.messages.

DECLARE @error int

INSERT...

SET @error = @@ERROR

SELECT [text]
FROM sys.messages
WHERE message_id = @error AND language_id = 1033