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
Best Answer
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:
Running it, like this, without a
TRY...CATCH
, allows all the statements in the proc to run:The output:
However, if you run it inside a
TRY...CATCH
:You see only the error message:
The
PRINT (N'Run the SELECT');
never runs, and indeed theSELECT 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:
As always, the devil is in the details.
TRY...CATCH
always aborts the code inside theBEGIN TRY...END TRY
code block if any error over severity 10 occurs that does not close the database connection. Execution is immediately passed into theBEGIN 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:
Using this stored proc, either inside a
TRY...CATCH
or with noTRY...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.