Weird TRY/CATCH behaviour using INSERT INTO

Transact-SQL
1

A combination of a INSERT INTO using a procedure as the source of data fails with a very strange error when the try/catch block inside the procedure is triggered by a conversion error. What am I missing here. Why am I getting this strange error?

Msg 3930, Level 16, State 1, Line 32
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Here is an example of a code with this behaviour, note that this is not the actual code where I encountered the error but a very simplified version I used to try to replicate the error:

CREATE OR ALTER PROCEDURE test
(
@make_error BIT = 0
)
AS
BEGIN
BEGIN TRY
IF @make_error = 1
-- casting error causes strange error
SELECT CAST('ble' AS INT)
ELSE
SELECT 1 AS id
END TRY
BEGIN CATCH
PRINT 'Error - in catch'
END CATCH
END
GO

DROP TABLE IF EXISTS #test
GO
CREATE TABLE #test (id INT)
GO

-- this works fine
EXEC test @make_error = 0
GO
EXEC test @make_error = 1
GO

-- this also works fine
INSERT INTO #test
EXEC test @make_error = 0
GO
-- but this causes strange error
INSERT INTO #test
EXEC test @make_error = 1
GO

2

Interesting. Looks like the error is caused by SQL's own error code.

I suggest adding an explicit "THROW" in the CATCH block because that should RAISE the original error.

BEGIN CATCH
PRINT 'Error - in catch'; /* you must add a semicolon before THROW */
THROW;
END CATCH
END

3

Adding the THROW will make the original error show but still does not fix my problem because it will kick me out of the procedure with an error instead of supressing the error. If I try to prevent that by adding another try/catch block around the original code I will end up with the same strange error as before

4

Some errors are totally CATCHable and some are not. This may be one of the ones that is not.

5

Add in SET XACT_ABORT ON; as well as the ;throw; statement.

That gives me the conversion error in the two SP calls and the other error goes away.

CREATE OR ALTER PROCEDURE test
(
@make_error BIT = 0
)
AS
BEGIN
SET XACT_ABORT ON;
BEGIN TRY
IF @make_error = 1
-- casting error causes strange error
SELECT CAST('ble' AS INT)
ELSE
SELECT 1 AS id
END TRY
BEGIN CATCH
PRINT 'Error - in catch'
;throw;
END CATCH
END
GO

Additional information here: http://www.sommarskog.se/error_handling/Part1.html#jumpXACT_ABORT

6

Speaking of Sommarskog, he has another article where he discusses INSERT..EXEC and its problems.

The issue OP showed is caused by the implicit transaction opened by the INSERT statement, which cannot be committed because of the error. But the error message that instructs the user to " Roll back the transaction." seems to be misleading because the transaction has been rolled back when the control is returned to the user.

For some time now, I have tried to avoid INSERT..EXEC where possible, instead opting for the process-keyed tables that Sommarskog discusses in the same article, especially if there are multiple callers.

2 Likes
7

I tried that but I am still getting an error when trying the INSERT-EXEC scenario unfortunately. It looks like there is no good solution to this so I think I just need to change tactics and use a shared temp table or an output table. But I read through the sommarskog article and it was very enlightening :slight_smile:

8

If you do want to make it work, wrap the insert in a TRY..CATCH block like this:

BEGIN TRY
    INSERT INTO #test
    EXEC test @make_error = 1
END TRY
BEGIN CATCH
    PRINT 'could not insert';
END CATCH