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
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