T-SQL - Incorporating Try-Catch and Error-Handling (Transactions)

Question

I am working on a Transaction using Repeatable Read Isolation Level. I want to incorporate both Try-Catch and an Error-handler features in this Transaction. When I run the code, I get an error message that : Msg 102, Level 15, State 1, Line 18 Incorrect syntax near 'BEGIN'. Msg 102, Level 15, State 1, Line 23 Incorrect syntax near '@errnum'.

How do I complete this Transaction successfully? OR What is the correct way to write this Transaction?

This is my work as at now:

CREATE PROCEDURE ItemFlow (@Name  VARCHAR(50),
                                @aPrice  MONEY,
                                @bPrice MONEY)
AS
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
  BEGIN TRAN
      IF EXISTS (SELECT 1
                 FROM   Cashier
                 WHERE  Item = '@Item')
        UPDATE Cashier
        SET    bPrice = '@bPrice',
               aprice = '@aprice'
        WHERE  Item = '@Item'
      ELSE
        INSERT INTO Cashier
                    (Item, aPrice, bPrice)
        VALUES      ('@Item', '@aPrice', '@bPrice')
                     END
BEGIN TRY
    EXECUTE ItemFlow
END TRY

BEGIN CATCH
    @errnum = ERROR_NUMBER(),
           @severity = ERROR_SEVERITY(),
           @errstate = ERROR_STATE(),
           @proc = ERROR_PROCEDURE(),
           @line = ERROR_LINE(),
           @message = ERROR_MESSAGE()

END CATCH

Answer 1

One problem is the GO that terminates the create procedure statement. I always use begin/end with stored procedures:

CREATE PROCEDURE ItemFlow (@Name  VARCHAR(50),
                           @aPrice  MONEY,
                           @bPrice MONEY)
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    BEGIN TRAN
      IF EXISTS (SELECT 1
                 FROM   Cashier
                 WHERE  Item = '@Item')
        UPDATE Cashier
        SET    bPrice = '@bPrice',
               aprice = '@aprice'
        WHERE  Item = '@Item'
      ELSE
        INSERT INTO Cashier
                    (Item, aPrice, bPrice)
        VALUES      ('@Item', '@aPrice', '@bPrice')
    COMMIT TRAN;
END;  -- ItemFlow

Then, you need arguments when you call the stored procedure, or default values defined for them:

BEGIN TRY
    EXECUTE ItemFlow @arg1, @arg2, @arg3
END TRY
BEGIN CATCH
    @errnum = ERROR_NUMBER(),
           @severity = ERROR_SEVERITY(),
           @errstate = ERROR_STATE(),
           @proc = ERROR_PROCEDURE(),
           @line = ERROR_LINE(),
           @message = ERROR_MESSAGE()

END CATCH;

Answer 2

Before implementing any of the suggested changes, please answer: 1) why you are calling the proc itself within the proc? and 2) why are you setting the ERROR_ functions into variables? Are you going to use them? If not, no need to declare variables.

Also:

• Obviously the GO can't be a part of this. It is just a batch separator for SSMS.

• You have an input param called @Name that is not used, and a variable called @Item that is not declared. I suspect they are the same thing so I have used @Item as the input param instead of @Name.

• You use the input params as string literals in the three queries, which makes no sense. You need to remove the single-quotes from around them so that they can act as variables.

• And, please do NOT separate the TRY / CATCH logic from the transaction!

Assuming that there was no real intention in calling an infinite loop (the proc calling itself with no condition to ever stop), your code should look as follows:

CREATE PROCEDURE ItemFlow
(
  @Item  VARCHAR(50),
  @aPrice  MONEY,
  @bPrice MONEY
)
AS
SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRY

  BEGIN TRAN

  IF EXISTS (SELECT 1
             FROM   Cashier
             WHERE  Item = @Item)
  BEGIN
        UPDATE Cashier
        SET    bPrice = @bPrice,
               aprice = @aPrice
        WHERE  Item = @Item;
  END;
  ELSE
  BEGIN
        INSERT INTO Cashier
                    (Item, aPrice, bPrice)
        VALUES      (@Item, @aPrice, @bPrice);
  END;

  COMMIT TRAN;

END TRY
BEGIN CATCH

  IF (@@TRANCOUNT > 0)
  BEGIN
    ROLLBACK TRAN;
  END;

  THROW;

END CATCH;

THROW was introduced in SQL Server 2012. If you are using anything from 2005 - 2008 R2, replace the THROW with:

DECLARE @ErrMessage NVARCHAR(4000);
SET @ErrMessage = ERROR_MESSAGE();
RAISERROR(@ErrMessage, 16, 1);
RETURN;

Answer 3

Your code has a few problems:

Error near BEGIN

GO is a batch separator. It's not valid T-SQL and only understood by SSMS. You are effectively submitting two queries:

CREATE PROCEDURE ItemFlow (@Name  VARCHAR(50),
                                @aPrice  MONEY,
                                @bPrice MONEY)
AS
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

and:

BEGIN TRAN
...

As you can see, the stored procedure body is empty. Remove GO to get rid of this.

Error near @errnum

Your variables are not declared. Also, you must use SELECT or SET to assign a value to these variables:

DECLARE @errnum int,
        @serverity int,
        (etc.)
        
SELECT @errnum = ERROR_NUMBER(),
       @severity = ERROR_SEVERITY(),
       @errstate = ERROR_STATE(),
       @proc = ERROR_PROCEDURE(),
       @line = ERROR_LINE(),
       @message = ERROR_MESSAGE()

One last thing:

You have a BEGIN TRAN but not a COMMIT TRAN. Your transaction is still open by the end of the sproc's execution.