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