|
|
Posted by Erland Sommarskog on 05/09/06 00:41
rlm (groups@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|