By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

TRANSACTIONS in a WHILE LOOP. Flow Question

P: n/a
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_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

May 8 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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_test, 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****@sommarskog.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
May 8 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.