Surprisingly, this is pretty much how it works although I thought that the delayed name resolution occurred during compile and not at execution.

In this situation, the Invalid Object error is sever enough to cause the stored procedure to just stop executing. This is why you're not seeing your error handler called.

>>Worse, the changes are committed eventhough there was an error! I suspect that there's an implicit COMMIT somewhere but have no idea where.

Are you sure that the changes were committed? You still have an active transaction, as indicated by the error message. You changes would still be in the cache and available for any queries on the same connection. The way to verify this would be to check the test4 table from another connection. I would expect that something like SELECT * FROM test4 executed on another connection would block due to the active transaction on the other connection and rolling back the transaction would revert the changes, release the locks, and you'd see the SELECT * execute.

Probably a better way to test your error handling would be to include a RAISERROR() instead of updating a bogus table. You could also create a constraint on the table and have your update violate the constraint.

-Mike

>Hi,
>
>I'm testing a simple sproc. Basically, I'm trying to wrap the entire sproc into a transaction and roll back everything if there's an error. Nothing fancy. The code below checks @@error after each statement and if there was an error, it calls the ERR_HANDLER which performs a ROLLBACK.
>
>The sproc works as expected if another user holds a lock to one of the records that we are trying to update.
>
>However, I've added some code that triggers a runtime error (the table doesn't exist). This triggers the expected error (invalid object), but it also reports that the transaction count is mismatched.
>
>Worse, the changes are committed eventhough there was an error! I suspect that there's an implicit COMMIT somewhere but have no idea where.
>
>Any ideas what's going on here?
>
>TIA
>
>Here are the error messages when a runtime error occurs:
>

>Server: Msg 208, Level 16, State 1, Procedure usp_t4, Line 16
>Invalid object name 'test40'.
>Server: Msg 266, Level 16, State 1, Procedure usp_t4, Line 26
>Transaction count after EXECUTE indicates that
>a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
>

>
>
>Here's the sproc:
>
>

>CREATE PROCEDURE usp_t4
>
>AS
>
>BEGIN TRANSACTION
>
>    UPDATE test4 SET cValue='sproc' WHERE nId=2
>    IF (@@ERROR <> 0) GOTO ERR_HANDLER
>
>    UPDATE test4 SET cValue='sproc' WHERE nId=3
>    IF (@@ERROR <> 0) GOTO ERR_HANDLER
>
>    UPDATE test4 SET cValue='sproc' WHERE nId=4
>    IF (@@ERROR <> 0) GOTO ERR_HANDLER
>
>    -- the following line triggers a runtime error since
>    -- table test40 doesn't exist
>    UPDATE test40 SET cValue='sproc' WHERE nId=5
>    IF (@@ERROR <> 0) GOTO ERR_HANDLER
>
>COMMIT TRANSACTION
>
>RETURN 0
>
>ERR_HANDLER:
>--RAISERROR('Stored procedure failed. Transaction has been rolled back.', 16, 1)
>ROLLBACK TRANSACTION
>RETURN 1
>GO
>