The error in the EXEC
part of the INSERT-EXEC
statement is leaving your transaction in a doomed state.
If you PRINT
out XACT_STATE()
in the CATCH
block it is set to -1
.
Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT
succeeds.
ALTER PROCEDURE test -- or create
AS
BEGIN try
DECLARE @retval INT;
DECLARE @t TABLE(x INT CHECK (x = 0))
INSERT INTO @t
VALUES (1)
SET @retval = 0;
SELECT @retval;
RETURN( @retval );
END try
BEGIN catch
PRINT XACT_STATE()
PRINT ERROR_MESSAGE();
SET @retval = -1;
SELECT @retval;
RETURN( @retval );
END catch;
Adding this to the CATCH
block
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION;
END;
Doesn't help. It gives the error
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC
anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.
DECLARE @RC INT;
EXEC sp_executesql
N'EXEC @RC = test',
N'@RC INT OUTPUT',
@RC = @RC OUTPUT;
INSERT INTO @t
VALUES (@RC)
Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.
DECLARE @RetVal INT = -1
IF OBJECT_ID('PrintMax', 'P') IS NULL
BEGIN
EXEC('create procedure PrintMax as begin print ''hello world'' end;')
SET @RetVal = 0
END
SELECT @RetVal;
RETURN( @RetVal );
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.
Best Answer
It depends on whether you want to abort the entire batch. If you want to entirely abort the batch and since you don't want to go ahead with try catch, XACT_Abort will do the trick for you:
Now try to execute the procedure and also you will see Select does not succeded
Further Throw, raiserror also gives me the same behaviour, but I always tend to use set
Xact_abort on
if I want my transaction to rollback entirely even in case of timeouts. Further MSDN page has more info:Below link gives you more idea on advantages of using xact_abort: What is the benefit of using “SET XACT_ABORT ON” in a stored procedure?