Menu
I am using SQL Server 2008 and when I run this Statement in Management studio the Select statement in the Catch Block is executed as expected
BEGIN TRY
INSERT INTO IDontExist(ProductID)
VALUES(1)
END TRY
BEGIN CATCH
SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH
However when I run this statement the statement in the Catch Block is never executed and instead the error is just displayed in the results tab
BEGIN TRY
Select * from IDontExist
END TRY
BEGIN CATCH
SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH
They both return the same error number '208' 'Invalid Object Name: IDontExist' so why would one get handled and the other not?
874
A TRY... CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.
Note that you cannot use TRY... CATCH blocks inside T-SQL UDFs. If you have to capture errors that occur inside a UDF, you can do that in the calling procedure or code.
To handle exception in Sql Server we have TRY.. CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks.
When called in a CATCH block, ERROR_MESSAGE returns the complete text of the error message that caused the CATCH block to run. The text includes the values supplied for any substitutable parameters - for example, lengths, object names, or times. ERROR_MESSAGE returns NULL when called outside the scope of a CATCH block.
I don't get the CATCH block hit at all.
That's because the code won't compile, because the object doesn't exist, no plan is generated, so nothing runs to hit the CATCH block.
You can never hit this catch block so somethign is wrong with your testing/example. You can hit an outer catch block in a different scope (eg nested stored procs)
Edit: I'm using SQL Server 2005 SP3
It depends when deferred name resolution applies, related to statement level recompilation.
In my case, the whole batch fails both times and no statement level recompilation happens so no deferred name resolution
In OP's case, the batch compiles and runs but then has a statement level recompilation/deferred name resolution error in running code
I'm off to find some references about why it's different, given BOL doesn't say much, neither does Erland Sommarskog
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
