4

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
  • 1
    Does SomeStoredProcedure return multiple result sets? In general, I avoid writing code that generates "expected errors" and simply discards them. Handling/preventing the errors upstream is generally the best way to handle that. – AMtwo Aug 25 '16 at 20:17
  • If error indicates that statement is terminated then nothing should be inserted in #temptable, unless you are doing multiple inserts. – Anton Krouglov Aug 25 '16 at 20:19
  • @MaxVernon Ah, I missed that bit. Seems my critical reading skills are lacking today. Still curious if the proc returns multiple result sets--which might at least explain why some results land in the #temptable in some scenarios – AMtwo Aug 25 '16 at 20:20
  • @AMtwo - resolved by checking the docs for TRY...CATCH - see my answer below. I'm removing my comments above. – Max Vernon Aug 26 '16 at 16:21
3

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.

  • Hi Max, try changing your SP to something like this: CREATE TABLE #TEMP(ID INT NOT NULL) INSERT INTO #TEMP VALUES(1) INSERT INTO #TEMP VALUES( 1 /0) SELECT * FROM #TEMP – tariktik Aug 25 '16 at 21:32
  • @tariktik - I've updated my answer to show why you are seeing the unexpected results. – Max Vernon Aug 26 '16 at 16:15
  • As a follow-up to the question, is there a way to retrieve both the result and error message? – tariktik Aug 26 '16 at 18:05
  • You'd need to modify the stored procedure to capture @@ERROR after each statement, and not use a TRY...CATCH construct. – Max Vernon Aug 26 '16 at 18:09
  • also, 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. – Max Vernon Aug 26 '16 at 18:12
0

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
  • Thanks Tara. I tried but @@ERROR does not return anything since the error is from within another stored procedure – tariktik Aug 25 '16 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.