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 ?
1. Nope. You should know in advance however if a table (or column) exists or 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.
> 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 ?
Jacco Schalkwijk (NOSPAMjac...@eurostop.co.uk) writes: > 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
Amy (l...@usa.com) writes: > 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.
-- Erland Sommarskog, SQL Server MVP, som...@algonet.se
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
> Jacco Schalkwijk (NOSPAMjac...@eurostop.co.uk) writes: > > 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
|
