Sql-server – TSQL success or failure result of EXEC dynamic SQL

error handlingsql servert-sql

Based on the following code:

/* This SP takes a query and a unique temporary table name as input... 
The outputs is a string with the command to create the tmp table based on the query that is given in the @SRC parameter 
*/ 
EXEC dbo.createTmpFromQuery @SRC,@uniqueTMP,@createTmpStr=@SQLStr OUTPUT
/* This executed the query that will create the tmp table and insert the data from @SRC */
EXEC(@SQLStr)

The whole stored procedure that calls these 2 lines (among many others) is in a Try Catch.
I've just run into a problem where the query in @SRC had a syntax error which called the Catch portion:

BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @ERRORMESSAGE=CASE WHEN @ERRORMESSAGE='' THEN ERROR_MESSAGE() ELSE @ERRORMESSAGE END 
        RAISERROR (@ERRORMESSAGE,16,1)
END CATCH

the Try has many validations and I set @ErrorMessage to personalized messages in most cases.
In this case, I would like to set a personalized message instead of getting the standard error message

(example): Incorrect syntax near 'FROM'.

I'm trying to figure out how I can get the EXEC(@SqlStr) return a successful or failure code, so I can set the @ErrorMessage before it goes into catch portion of the code.

Best Answer

I found the solution... I wrapped the statements in another try catch within the big try catch (Didn't know we can do that) :-(

    BEGIN TRY 
        EXEC dbo.createTmpFromQuery @SRC,@uniqueTMP,@createTmpStr=@SQLStr OUTPUT
        EXEC(@SQLStr)
    END TRY
    BEGIN CATCH 
        SET @ErrorMessage='Error Creating '+@uniqueTMP+' table - verify query in @SRC'
        RAISERROR(@ErrorMessage,16,1)
    END CATCH

I tested it an it worked. I hope this will help someone else...

Related Solutions

Sql-server – Rollback group of DDL statements

The reason you're seeing this result is that SQL Server is not actually catching your ALTER TABLE error. You'll notice that when you run this, you see the red error message rather than a printed line--you can verify this by changing print @@error to something like print 'HELLO!'; in that case, you will NOT see 'HELLO!' printed; you will see the error instead. Books Online has a list of cases of errors which you cannot catch.

An alternative here is to SET XACT_ABORT ON before you begin your transactions. Then, you can rollback your changes upon getting your first error.

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

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

Best Answer

Related Solutions

Sql-server – Rollback group of DDL statements

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

Related Question