When I attempt to manage Transactions in a WHILE LOOP @@TRANCOUNT is
off. I obviously do not understand error handling as I should. In the
loop below where does the point of execution move to after an error?
RESULT:
Server: Msg 266, Level 16, State 1, Procedure flowcontrol_test, Line
111
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 0, current count =
1.
CREATE PROCEDURE flowcontrol_test
AS
-- EXECUTE SOME CODE
WHILE @current_Row <= @row_Count
BEGIN
IF BLAH -- Some Conditional
BEGIN
-- Do some other stuff
BEGIN TRAN tran_TEST
-- X Doesn't Exist. Where does the point of execution
go???
UPDATE X
SET update_dtm = @dateTimeStamp
WHERE v = @V
SET @errorCode = @@ERROR
IF @errorCode = 0
BEGIN
-- -- intentional error introduced. Where does
the point of execution go???
UPDATE tbl
SET field_with_noncompatible_data_type =
@dateTimeStamp
WHERE v = @V
SET @errorCode = @@ERROR
IF @errorCode = 0
BEGIN
-- intentional error introduced. Where does
the point of execution go???
DELETE FROM child
WHERE child key
EXISTS in parent table
SET @errorCode = @@ERROR
END
END
IF @errorCode = 0
COMMIT TRAN tran_TEST
ELSE
ROLLBACK TRAN tran_TEST
END
BLAH -- RESET SOME STUFF
END
