March 1, 2012 at 12:06 pm
I have not been able to find any documentation that specifically states what defines a statement_block. I know that "BEGIN...END" do, as shown by the following example. Because @MyInt = 1, both statements after the BEGIN will be executed. If we change value to 0, both of the first statements are not executed but the code under the ELSE condition will execute:
DECLARE@MyIntINT = 1
IF@MyInt = 1
BEGIN
PRINT'Begin: Int = 1'
PRINT'Begin: It really does.'
END
ELSE
PRINT'Begin: Int <> 1'
GO
It also appears that a "TRY...CATCH" block will define a statement_block. Again, both lines will under the true case will be executed and the ELSE case will not, showing that the compiler correctly correlates the IF and ELSE:
DECLARE@MyIntINT = 1
IF@MyInt = 1
BEGINTRY
PRINT'Try: Int = 1'
PRINT'Try: It really does.'
ENDTRY
BEGINCATCH
PRINT'In Catch'
ENDCATCH
ELSE
PRINT'Try: Int <> 1'
GO
However, a "BEGIN TRAN...COMMIT" does not seem to define a block. The following code gets a compile error before execution:
DECLARE@MyIntINT = 1
IF@MyInt = 1
BEGINTRAN
PRINT'Tran: Int = 1'
PRINT'Tran: It really does.'
COMMITTRAN
ELSE
PRINT'Tran: Int <> 1'
GO
Even thought "BEGIN" is part of the statement, it seems that SQL Server does not consider the "BEGIN TRAN" statement to start a "statement_block" of code. That makes sense in that a transaction may be started in one area and completed in another area so it does not need to be matched to the the COMMIT for compilation and execution.
So, my questions are:
1) Is there documentation on what can define a statement_block?
2) Is "TRY...CATCH" intended to define a statement_block or is that an accident that may be removed later?
3) Are there any other statements that define a statement_block?
March 1, 2012 at 1:28 pm
fahey.jonathan (3/1/2012)
So, my questions are:1) Is there documentation on what can define a statement_block?
2) Is "TRY...CATCH" intended to define a statement_block or is that an accident that may be removed later?
3) Are there any other statements that define a statement_block?
I think that it's pretty clear:
From the documentation for IF...ELSE
To define a statement block, use the control-of-flow keywords BEGIN and END.
From the documentation for WHILE
To define a statement block, use the control-of-flow keywords BEGIN and END.
Although there is a slight discrepancy if you look at the documentation for TRY...CATCH
Any group of Transact-SQL statements in a batch or enclosed in a BEGIN…END block.
The documentation for IF...ELSE notes that "Unless a statement block is used, the IF or ELSE condition can affect the performance of only one Transact-SQL statement." This condition actually makes sense, since the END TRY/BEGIN CATCH/END CATCH are all required parts of the TRY...CATCH statement whereas ELSE is an optional part of the IF...ELSE and WHILE doesn't have any secondary parts. IF...ELSE can't be defined using a batch because the ELSE is optional, and similarly with WHILE.
I think where you are getting confused is that TRY...CATCH does not define a statement block. It is a SQL statement that may contain a statement block. BEGIN TRAN is not a container, so it cannot contain a statement block.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 1, 2012 at 1:50 pm
As you can see from my second example above, there is no "BEGIN...END", yet the two print statements act as a single block. That means that "BEGIN...END" is not required to create a "statement_block". The "TRY...CATCH" is acting as the mechanism that groups the two statements into a single "block" under the "IF" portion of the "IF...ELSE" statement.
As you correctly pointed out, the documentation for "IF...ELSE" says, "To define a statement block, use the control-of-flow keywords BEGIN and END." However, I used the "TRY...CATCH" instead. Is this documented anywhere? Can I rely on "TRY...CATCH" to block several statements under an "IF" or should I use both as shown below?
DECLARE@MyIntINT = 1
IF@MyInt = 1
BEGINTRY
BEGIN-- Is this "BEGIN" necessary?
PRINT'Try: Int = 1'
PRINT'Try: It really does.'
END
ENDTRY
BEGINCATCH
PRINT'In Catch'
ENDCATCH
ELSE
PRINT'Try: Int <> 1'
GO
Perhaps:
DECLARE@MyIntINT = 1
IF@MyInt = 1
BEGIN-- Is this "BEGIN" necessary?
BEGINTRY
PRINT'Try: Int = 1'
PRINT'Try: It really does.'
ENDTRY
BEGINCATCH
PRINT'In Catch'
ENDCATCH
END
ELSE
PRINT'Try: Int <> 1'
GO
EDIT:
I think where you are getting confused is that TRY...CATCH does not define a statement block. It is a SQL statement that may contain a statement block.
You say that a "TRY...CATCH" does not define a block, but I have done just that. There are two statements that will be executed if the "IF" clause is true, and the only thing grouping them together is the "TRY...CATCH".
March 2, 2012 at 11:47 am
Let me see if I can rephrase this so that it makes sense.
DECLARE@MyIntINT = 1
IF@MyInt = 1
BEGIN
PRINT'Begin: Int = 1'
PRINT'Begin: It really does.'
END
ELSE
PRINT'Begin: Int <> 1'
This code is parsed as
IF <statement-block> ELSE <statement>
The following code looks similar, so it should be parsed the same, right? WRONG?
DECLARE@MyIntINT = 1
IF@MyInt = 1
BEGINTRY
PRINT'Try: Int = 1'
PRINT'Try: It really does.'
ENDTRY
BEGINCATCH
PRINT'In Catch'
ENDCATCH
ELSE
PRINT'Try: Int <> 1'
This code is parsed as
IF <statement> ELSE <statement>
The following code is a single, complex statement. It is not a statement block even though it contains one. TRY...CATCH does not define a statement block, it is a single statement that may contain a statement block.
BEGIN TRY
PRINT'Try: Int = 1'
PRINT'Try: It really does.'
END TRY
BEGIN CATCH
PRINT'In Catch'
END CATCH
It's mostly a matter of semantics. Yes, the two PRINT statements are in a statement block, but the statement block is defined by a batch, not by the TRY...CATCH block, however the fact that you can use a batch instead of explicitly using BEGIN...END is due to the fact that it's contained in a TRY...CATCH block.
Does that make sense?
Drew
Edited to fix formatting issue.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 2, 2012 at 12:12 pm
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
Is your point that "TRY...CATCH" is a single statement so it falls under the "sql_statement" option rather than the "statement_block" option?
March 2, 2012 at 12:51 pm
fahey.jonathan (3/2/2012)
IF Boolean_expression{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
Is your point that "TRY...CATCH" is a single statement so it falls under the "sql_statement" option rather than the "statement_block" option?
Essentially. Does that answer your question?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 2, 2012 at 1:31 pm
It's like with a procedure.
CREATE PROCEDURE ... doesn't define a statement block, but this prints out all the print statements
CREATE PROCEDURE Talkative AS
Print '1'
Print '2'
print '3'
GO
DECLARE@MyIntINT = 1
IF@MyInt = 1
EXEC Talkative
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply