Sql-server – Purpose of Try/Catch without Transactions

error handlingsql serversql-server-2016

I have come across the following code (SQL Server 2016, 2008-compatible database):

CREATE PROCEDURE myProc
AS BEGIN
    DECLARE @ERRORMESSAGE As VARCHAR(1000)

    BEGIN TRY   
        DELETE FROM Foo -- (statement #1)
        DELETE FROM Bar -- (statement #2)
    END TRY
    BEGIN CATCH
        SET @ERRORMESSAGE = ERROR_MESSAGE()
        RaisError(@ERRORMESSAGE,16,1) 
    END CATCH   
END

What is the purpose of the try/catch block?

For the sake of discussion, lets assume there are no triggers called and the client code that executes this procedure does not wrap the call in a transaction.

I had to fix a bug in code similar to this where statement #2 raised an error. Looking at the data it appeared statement #1 completed successfully and statement #2 did not apply (obviously).

One thing this does is not give a clear line number when trying to debug the crash, but is there anything legitimate this does?

Best Answer

IMHO Using a TRY/CATCH block you don't need to check every single operation.

DELETE FROM Foo;
IF @@ERROR <> 0
BEGIN
    RAISERROR('Error deleting table Foo', 16, 1);
    RETURN -1;
END

OR

DELETE FROM Bar;
IF @@ERROR <> 0
BEGIN
    SET @ErrMsg = 'Error';
    GOTO ERROR;
END

Related Solutions

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

Sql-server – Moving away from CmdExec steps in job without losing logging detail (SQL Server 2008)

Set the output of the T-sql job step to output to a file. It's on the advanced tab of the job step properties.

Best Answer

Related Solutions

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

Sql-server – Moving away from CmdExec steps in job without losing logging detail (SQL Server 2008)

Related Question