Errors That stop execution of SP.
Amy
I found that referring to a table that does not exist or is inaccessible
from a SP stops the execution of the SP and returns the error.
select * from <nonexists>
OR
select * from <linked server>.db.owner.table when the linked server is
inaccessible for some reason.
This prevents me from handling the error inside the SP code.
I found that i can solve it by using dynamic execution - EXEC
(select......).
I have 3 questions :
1. Is there a better way to handle this situation ?
2. Are there any more error situation that cause the SP to stop execution
and return ?
3 Do the same rules apply to UDF and Triggers ?
Thanks,
Amy
Jacco Schalkwijk
not. You can always find out with something like IF EXISTS(SELECT * FROM
information_schema.tables where table_name = <table_name>, but basically
your database schema should be stable and tables shouldn't just disappear. A
linked server being unreachable is a runtime error, and you can't prepare
for that.
2. Yes, any error returned by something outside SQL Server will terminate
the batch, i.e. stop the stored procedure execution. This includes BULK
INSERT, extended procedures, calling COM objects with the sp_OA% stored
procedures, etc. Also if you try to access columns that do not exists this
will terminate the batch as well.
3. Triggers and UDF's are worse. You can't trap _any_ errors inside them.
Errors in UDF's and trigger will terminate the batch and rollback any open
transactions. As UDF's and triggers are supposed to work like normal
database functions, tables and constraints that makes a lot of sense,
because SQL Server does not know with the current error handling if an error
is actually handled, and having a possibly unhandled error in what should be
an atomic, consistent transaction is a no-go.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Amy" <l...@usa.com> wrote in message
news:uV95wxhS...@TK2MSFTNGP12.phx.gbl...
Erland Sommarskog
> Also if you try to access columns that do not exists this
> will terminate the batch as well.
No, access to a non-existing column that was not detected when you loaded
the procedure because of deferred name resolution will not abort the
batch. Execution of the current scope is aborted, but execution continues
in the calling scope. Note that if the aborted procedure started a
transaction, this transaction is not rolled back.
This applies to all compilation errors that occurs during run-time.
> 3. Triggers and UDF's are worse. You can't trap _any_ errors inside
> them. Errors in UDF's and trigger will terminate the batch and rollback
> any open transactions. As UDF's and triggers are supposed to work like
> normal database functions, tables and constraints that makes a lot of
> sense, because SQL Server does not know with the current error handling
> if an error is actually handled, and having a possibly unhandled error
> in what should be an atomic, consistent transaction is a no-go.
When an error occurs in a trigger, or in some code invoked by the trigger,
execution is aborted immediately, and the transaction is rolled back. With
two exception: RAISERROR does not terminate the batch, and neither does
error 266, mismatch in @@trancount, because this error is simply not raised
at all when in trigger context.
UDFs are not like that. They work like stored procedures. That is, if the
error is one that aborts the batch is aborted, else execution continues.
Except that, when a UDF is invoked as part of an INSERT, UPDATE, DELETE
SELECT and SET statement, that statement is terminated immediately, so
you cannot trap the error inside the UDF. Worse is, you cannot trap
the error after the aborted statement either, because at that point
@@error is 0! (This applies for scalar UDF and multi-statement UDF, not
for inline UDF. For them @@error is set.)
If you invoke a UDF with EXEC (possible with scalar UDF), execution
continues in case of a such an error, and you can inspect @@error to
find out.
--
Erland Sommarskog, SQL Server MVP, som...@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Erland Sommarskog
> 1. Is there a better way to handle this situation ?
Yes. Load your procedures with AbaPerls, then you will be alerted when
you load the procedures, that the tables are missing. AbaPerls is a toolset
that we use in our shop, and that I have made available as freeware. You
find it on http://www.abaris.se/abaperls(
> 2. Are there any more error situation that cause the SP to stop
> execution and return ?
All compilations errors, such missing column, missing alias etc that were
not detected when you loaded the procedure, causes the procedure to abort
and execution continue in the caller.
There is also a set of errors that causes the entire batch to be
aborted, and any out-standing transaction to be rolled back.
> 3 Do the same rules apply to UDF and Triggers ?
Yes. Except that any error in a trigger causes the batch to be aborted
immediately.
Jacco Schalkwijk
Thanks for your corrections. I didn't have a clear distinction between scope
and batch, that is what made my answer incorrect. It is good to see this
information summed up, because it is very hard to come by.
Regards,
Jacco
"Erland Sommarskog" <som...@algonet.se> wrote in message
news:Xns93BA7558...@127.0.0.1...