2005 TRY-CATCH BLOCKS - QUESTION

Hi,
I have some questions about the new TRY-CATCH block syntax in SQL
Server 2005.

>From an article online:
Not all errors can be trapped by a TRY/CATCH block. The first types of
errors that are not trapped are compile errors, like a syntax error
within a batch of T-SQL statements. Another type of error is deferred
name resolution errors created by statement level recompilations. If a
process is terminated by a KILL command then a TRY/CATCH block does not
capture this error. Client interrupt requests or broken client
connections are also not trapped by the TRY/CATCH block. For errors
that are not trapped, SQL Server 2005 passes control back to the
application immediately, without executing any CATCH block code.
1.) After reading this, I am confused. For non-trappable errors, does
this mean that statements that occurred before the error are committed,
then control is passed back to the application? Or does it rollback
everything, then pass control to the application?
2.) Does every single DML statement require its own TRY-CATCH block,
just like you check for @.@.ERROR after every stmt (in previous versions
of SQL Server)?
3.) Also, how can you return an error code from within the CATCH block?
I can't seem to find this information.
THANKS MUCH1. Unless included in a TRANSACTION, statements before the error that
completed without error are most likely, 'committed'.
2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
block. The TRY block may have multiple statements. So it is not necessary to
test the @.@.ERROR value after each separate statement execution as was
previously the case.
3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
@.RETURN value of the stored procedure. UNLESS, of course, you have created
your own custom error messages/number and you use RAISERROR to pass that
back to the client.
CATCH
RETURN error_number()
END
The .NET application can capture the @.RETURN parameter and evaluate the
value, making decisions as appropriate. @.RETURN = 0 signifies no error.
For more information, see:
http://msdn2.microsoft.com/en-us/library/ms175976.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegroups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>|||<tootsuite@.gmail.com> wrote in message
news:1159384855.362218.309450@.i42g2000cwa.googlegroups.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
TRY CATCH has no transactional semantics. It's purely about
control-of-flow.

> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
>
No. That's really the whole point of TRY/CATCH. You can have one bit of
error handling code get the errors for multiple TSQL statements, including
any invoked procedure.

> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
You don't return "error codes" any more. If you want to signal the calling
code or client that something went wrong, use RAISERROR.
David|||In addition to the other posts, many of these thing are easy to try. The fir
st one, for example.
Already answered by David: TRY-CATCH is only about flow control. If batch is
terminated after BEGIN
TRAN so that no COMMIT or ROLLBACK was never executed, you are left with an
open transaction:
begin try
BEGIN TRAN
SELECT * FROM lklklk
COMMIT TRAN
END TRY
BEGIN catch
ROLLBACK
RAISERROR('My user error', 16, 1)
END CATCH
PRINT 'After the whole lot'
GO
SELECT @.@.TRANCOUNT
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g2000cwa.googlegroup
s.com...
> Hi,
> I have some questions about the new TRY-CATCH block syntax in SQL
> Server 2005.
>
> Not all errors can be trapped by a TRY/CATCH block. The first types of
> errors that are not trapped are compile errors, like a syntax error
> within a batch of T-SQL statements. Another type of error is deferred
> name resolution errors created by statement level recompilations. If a
> process is terminated by a KILL command then a TRY/CATCH block does not
> capture this error. Client interrupt requests or broken client
> connections are also not trapped by the TRY/CATCH block. For errors
> that are not trapped, SQL Server 2005 passes control back to the
> application immediately, without executing any CATCH block code.
> 1.) After reading this, I am confused. For non-trappable errors, does
> this mean that statements that occurred before the error are committed,
> then control is passed back to the application? Or does it rollback
> everything, then pass control to the application?
> 2.) Does every single DML statement require its own TRY-CATCH block,
> just like you check for @.@.ERROR after every stmt (in previous versions
> of SQL Server)?
> 3.) Also, how can you return an error code from within the CATCH block?
> I can't seem to find this information.
> THANKS MUCH
>|||Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
Thanks
Tibor Karaszi wrote:
> In addition to the other posts, many of these thing are easy to try. The =
first one, for example.
> Already answered by David: TRY-CATCH is only about flow control. If batch=
is terminated after BEGIN
> TRAN so that no COMMIT or ROLLBACK was never executed, you are left with =
an open transaction:
> begin try
> BEGIN TRAN
> SELECT * FROM lk=F6lk=F6lk
> COMMIT TRAN
> END TRY
> BEGIN catch
> ROLLBACK
> RAISERROR('My user error', 16, 1)
> END CATCH
> PRINT 'After the whole lot'
> GO
> SELECT @.@.TRANCOUNT
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1159384855.362218.309450@.i42g=
2000cwa.googlegroups.com...[vbcol=seagreen]|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O4wHG#m4GHA.3960@.TK2MSFTNGP02.phx.gbl...
> 1. Unless included in a TRANSACTION, statements before the error that
> completed without error are most likely, 'committed'.
> 2. TRY-CATCH will divert control to the CATCH block upon error in the TRY
> block. The TRY block may have multiple statements. So it is not necessary
> to test the @.@.ERROR value after each separate statement execution as was
> previously the case.
> 3. In my opinion, the 'best' way to return an @.@.ERROR value is to use the
> @.RETURN value of the stored procedure. UNLESS, of course, you have created
> your own custom error messages/number and you use RAISERROR to pass that
> back to the client.
> CATCH
> RETURN error_number()
> END
> The .NET application can capture the @.RETURN parameter and evaluate the
> value, making decisions as appropriate. @.RETURN = 0 signifies no error.
> For more information, see:
> http://msdn2.microsoft.com/en-us/library/ms175976.aspx
>
IMO this is a very bad plan. Unless the .NET application is coded "just so"
it will never no that the command failed. IMO you should always use
RAISERROR to signal failure to the client. In the case of a .NET client,
this will cause a SqlException to be thrown, and invoke .NET's Try/Catch
exception handling.
David|||<tootsuite@.gmail.com> wrote in message
news:1159391039.519006.292410@.i42g2000cwa.googlegroups.com...
Ok, I guess that is where I was confused. I thought TRY blocks were for
error handling, not simply control of flow.
So that means, if I want to perform error handling, I still need to use
@.@.ERROR syntax, correct ?
Or is there some concept I am missing here?
...
TRY blocks control-of-flow constructs specially designed for error handling.
In TSQL they don't actually _perform_ the error handling, or rollbacks: You
have to code that in the CATCH block. But you should absolutely use them
instead of checking @.@.ERROR after each statement.
David