I'm trying understand how try ... catch construction works in T-SQL.
So I've read article at MSDN: http://msdn.microsoft.com/en-us/library/ms175976.aspx
I'm a bit confusing with this statement: A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.
It's saying that try ... catch can't span 2 BEGIN...END blocks and cannot span an IF...ELSE construct.
However, I've tried it and it works! (on both 2005 and 2008)
Can someone explain why? It looks like some mistake at the MSDN.
Look at the my test script:
print 'start'
begin transaction test_tran
begin try
-- first begin ... end
begin
-- some statements
print 'begin ... end - #1'
end
-- second begin ... end
begin
print 'begin ... end - #2'
-- statement with error
select 1/0 -- division by zero
end
print 'end of try'
end try
begin catch
print 'catch'
goto RollbackTran
end catch
-- commit
print 'commit'
commit transaction test_tran
-- rollback
RollbackTran:
BEGIN
print 'rollback'
WHILE @@TRANCOUNT > 0
begin
ROLLBACK TRANSACTION test_tran
print 'actual rollback'
end
END
Results:
start
begin ... end - #1
begin ... end - #2
(0 row(s) affected)
catch
rollback
actual rollback
It works (2 begin...end blocks)! But MSDN is saying that it should not. Same thing for IF ... ELSE statement.
Can someone explain it?
A T-SQL batch is most typically denoted by a GO statment. They are saying that you cannot do this:
BEGIN TRY
PRINT 'statement 1'
GO -- Cannot have a GO to end batch inside of a TRY / CATCH
PRINT 'statement 2'
END TRY
BEGIN CATCH
PRINT 'Catch Block'
END CATCH
Likewise you cannot:
IF (1 = 1)
BEGIN
BEGIN TRY
PRINT 'test'
END -- Cannot END a block that began prior to the TRY / CATCH
END TRY
BEGIN CATCH
PRINT 'Catch block'
END CATCH
nor can you do this:
IF (1 = 1)
BEGIN TRY
PRINT 'statement 1'
ELSE -- Cannot do ELSE for an IF that started prior to the TRY / CATCH
PRINT 'statement 2'
END TRY
BEGIN CATCH
PRINT 'Catch Block'
END CATCH
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