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 );
As long as you have your database organized in such a way that there is an account that is the owner of the tables and other objects, and your application connects using a separate account you can do what you want.
If you have tables and procedures organized in separate owners, you fist have to grant the required tables to the procedure owner (directly granted). Next you can grant the execute to a role or a user.
As soon as you allow connections to an object owner your security is broken since in that case you can always use all the objects of the owner (you are owner so you can use it).
Best Answer
I used this feature about 6 years ago. You are right, we cannot do this:
and
the same as
Why did we use it? We have a lot of algorithms (strategies) to calculate data, so we can easily switch between versions without changing the call interface.