Error "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements"

Question

I'm getting the error on the title when trying to execute the t-sql stored procedure

EXEC Add500ToChecking 18568

I tried deleting the return and the go statements but it didn't work

USE [JProCo] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[Add500ToChecking] @CustID INT AS BEGIN TRAN UPDATE SavAccount SET Balance = Balance - 500 WHERE CustomerID = @CustID IF ( (SELECT Balance FROM SavAccount WHERE CustomerID = @CustID) < 0) ROLLBACK TRAN RETURN UPDATE CkAccount SET Balance = Balance + 500 WHERE CustomerID = @CustID COMMIT TRAN RETURN GO

Any idea of what's going on with my code? This is for a class project

Make a habit of surrounding the conditional block with BEGIN and END. — Dan Guzman
– Dan Guzman, Commented Sep 29, 2017 at 21:00
Thanks for the advice, I will. That code is verbatim from the class test book (joes2pros), so it took me some time to ask for help here :) — Eduardo A. Fernández Díaz
– Eduardo A. Fernández Díaz, Commented Oct 6, 2017 at 3:17
Not the first time I've seen code bugs slip through tech edits. In addition to BEGIN/END, I suggest following an indention style and using semi-colon statement terminators, That makes intent clearer and code easier to maintain. — Dan Guzman
– Dan Guzman, Commented Oct 6, 2017 at 12:22

Siyual · Accepted Answer · 2017-09-29 20:59:31Z

You need a pair of BEGIN and END for your IF statement:

UPDATE SavAccount SET Balance = Balance - 500 WHERE CustomerID = @CustID IF ( (SELECT Balance FROM SavAccount WHERE CustomerID = @CustID) < 0) BEGIN ROLLBACK TRAN RETURN END

As written, only the ROLLBACK TRAN is part of the IF statement, so if it fails the IF check, it doesn't rollback the transaction, but immediately executes RETURN.

Probably should re-word "so if it fails the if check" to "so if it does not meet the if condition". In this example, the condition is looking for a failure so that it can do something, so it's slightly ambiguous what was meant.

Collectives™ on Stack Overflow

Error "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements"

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related