July 3, 2008 at 4:17 am
Hi,
If I have the following SQL (see below) why does it never hit the CATCH block? Obviously the error is deliberate.
BEGIN TRY
CREATE TABLE #T1(COLA VARCHAR(100))
INSERT INTO #T1 SELECT 'A', 'B'
END TRY
BEGIN CATCH
PRINT @@ERROR
END CATCH
Cheers
July 3, 2008 at 5:42 am
Some errors immediately get passed out of the code block to the calling procedure/application (they don't get caught by the catch at the same level as the code block). In your example code these sort of errors will not be caught by the try..catch and get passed to the query window where you see the error. In this code the number of columns is being checked at run time and the error generated is not one that catch will handle at the same level so it gets passed to the level above
July 3, 2008 at 5:47 am
Thanks for your reply DNA.
Would you (or anyone) agree that BOL is not clear about this? Or have I missed something?
Cheers
July 3, 2008 at 7:36 am
No, it's pretty clear. Here's the first statement that jumps out:
A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.
And then, further down:
Compile errors, such as syntax errors, that prevent a batch from executing.
You're generating a compile error.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 3, 2008 at 7:40 am
This does parse, so i'm guessing the compile is something else then?
in my actual code (not the snippet i posted) i get the data from a text file and its defined in a shema.ini file so if some one messes up the file it fails!
this code will compile into a stored procedure with outproblem, only at runtime does it fail.
July 3, 2008 at 7:56 am
It does parse, but try generating an estimated execution plan. It fails immediately. Parse and compile aren't the same.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 3, 2008 at 8:00 am
Thanks Grant.
Much appreciated!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply