TRANSACTIONS in a WHILE LOOP. Flow Question

[rlm]

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_tes t, 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_tes t
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_nonc ompatible_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

[Erland Sommarskog]

rlm (gr****@rlmoore .net) writes:

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_tes t, Line
111
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 0, current count =
1.
If I understand this correctly, you get an error here:
-- X Doesn't Exist. Where does the point of execution go???
UPDATE X
SET update_dtm = @dateTimeStamp
WHERE v = @V

Answer: to the next line in the *calling* procedure.

Error handling in SQL Server 2000 is a fairly messy story, and you cannot
always catch an error that occurs in a procedure in the procedure itself -
or even in T-SQL at all.

I have two articles on my web site on this:
http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html

It's a lot better in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx