October 20, 2011 at 8:46 am
I have written a stored procedure where I am using a TRY .. CATCH block, but that CATCH wasn't being executed when it should so I went to BOL to see what I was not doing right (search 'Using TRY...CATCH in Transact-SQL').
This example works
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GO
BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
I get what I expect
but this example
BEGIN TRY
-- This PRINT statement will run because the error
-- occurs at the SELECT statement.
PRINT N'Starting execution';
-- This SELECT statement will generate an object name
-- resolution error because the table does not exist.
SELECT * FROM NonExistentTable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
returns
Starting execution
Msg 208, Level 16, State 1, Line 8
Invalid object name 'NonExistentTable'.
I can't see my original stored procedure working if this example doesn't so any idea why this simple example doesn't work? Is there some instance/database setting needed?
I'm on a SQL Server 2005 SP3 database.
October 20, 2011 at 8:52 am
BOL states
Unlike the syntax error in the previous example, an error that occurs during statement-level recompilation will not prevent the batch from compiling, but it will terminate the batch as soon as recompilation for the statement fails. For example, if a batch has two statements and the second statement references a table that does not exist, deferred name resolution causes the batch to compile successfully and start execution without binding the missing table to the query plan until that statement is recompiled. The batch stops running when it gets to the statement that references the missing table and returns an error. This type of error will not be handled by a TRY…CATCH construct at the same level of execution at which the error occurred. The following example demonstrates this behavior.
i presume this is your reference website
October 20, 2011 at 8:53 am
It all has to do with the inner workings of the sql compiler. When you try to execute your example it can't compile because the table doesn't exist. It is unable to generate an execution plan so it throws an error. Does that make sense now?
Just curious, did you try the first example? It should work fine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 20, 2011 at 8:57 am
to add to my first comment, you will need to follow the example that follows, from the same website:
USE AdventureWorks2008R2;
GO
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ('usp_MyError', 'P') IS NOT NULL
DROP PROCEDURE usp_MyError;
GO
CREATE PROCEDURE usp_MyError
AS
-- This SELECT statement will generate
-- an object name resolution error.
SELECT * FROM NonExistentTable;
GO
BEGIN TRY
-- Run the stored procedure.
EXECUTE usp_MyError;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
October 20, 2011 at 9:07 am
Well, reading is under rated as a skill. I had missed that sentence (and the solution below). Many thanks, it all makes sense to me now. Catch blocks can't catch everything.
I do have a little issue with BOL's examples where they show bits of code that don't work and don't make it clear in the code comments that it will not work. I like to look at a section, see the examples, get the idea and use it without reading all the explanation (and the bit that says it doesn't work).
October 20, 2011 at 9:18 am
The first example worked fine, Sean. it will be inconvient to create another stored procedure to handle the error, but that's the best you can get.
BTW, I'm passing a database name on a linked server into the stored procedure and using it to dynamically create synonyms to access the tables in it. I then check that the database exists by doing a 'select count(*) from Synonym' that will return an error. I suppose I could use the data dictionary on the linked server before I create the synonyms. I'm musing now, better get on!
October 20, 2011 at 10:07 am
I did have to change the code to
declare @sql nchar(1000)
select @sql = 'select * from sdfad'
but creating and running dynamic SQL is a good trick to get an error to be caught.
sp_executesql is new to me too, so today is a learning day.
In my case the code is not going to be run much so performance isn't an issue for me.
I have just finished the procedure, using dblink.master.sys.databases where name = 'new database name' to determine whether the name is valid or not.
Thank you DavidAndrew13
October 21, 2011 at 8:36 am
glad to have helped.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply