1

The BEGIN...END description of TechNet make me confused with following remark:

Although all Transact-SQL statements are valid within a BEGIN...END block, certain Transact-SQL statements should not be grouped together within the same batch (statement block).

https://technet.microsoft.com/en-us/library/aa225998(v=sql.80).aspx

Could anybody tell me why they shouldn't be grouped within a BEGIN ... END block? Is there any problem when I use BEGIN...END to create region as this suggestion: sql server #region?

Community
  • 1
  • 1
Jacob Phan
  • 732
  • 2
  • 6
  • 20
  • 1
    Did you read (for example) the article that it links to: https://technet.microsoft.com/en-us/library/aa172435%28v=sql.80%29.aspx ? – Allan S. Hansen May 04 '16 at 05:56
  • I read but I don't understand. Could you explain more detail? – Jacob Phan May 04 '16 at 06:05
  • Basically - by grouping in BEGIN/END, you make the choice about how a batch should be, but you should only do so if intended (error handling,execution logic etc), and not unintended because of "appearance" which then can lead to the side effects explained. – Allan S. Hansen May 04 '16 at 06:21

3 Answers3

1

BEGIN..END is not a visual option in order to be able to collapse a region, you're actually telling the server that these bunch of lines are together. Using it as a #region like is just a Workaround.

This is because Whatever is between the BEGIN and END Will be executed as a Batch, and all the rules of a batch will apply to it.

Think of what will happen when an error occurs in one of the statements, do you want the other statements to continue ?

Check More here : Batches

Zein Makki
  • 29,485
  • 6
  • 52
  • 63
  • Not entirely true, if you do not separate commands with GO, the rest of T-SQL will not execute regardless on BEGIN ... END blocks. – Vojtěch Dohnal May 04 '16 at 06:10
  • Transactions also changes the behavior of the previous statements, but this is off-topic. Also based on the docs : "A few run-time errors, such as constraint violations, stop only the current statement. All the remaining statements in the batch are executed." – Zein Makki May 04 '16 at 06:14
1

If you need to create multiple batches in T-SQL script, you need to separate the group of commands explicitly with the GO statement. So BEGIN...END block (batch) is created implicitly even if you do not add it explicitly to your code. So adding it explicitly shoud not create any additional troubles for you.

Certain commands cannot be grouped together in one batch, as specified in the linked MSDN article:

CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must begin the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.

A table cannot be altered and then the new columns referenced in the same batch.

If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

Vojtěch Dohnal
  • 7,867
  • 3
  • 43
  • 105
0

You can use comments to distinguish different batches like the below.

BEGIN /** Region1 Starts**/
....
....
....
END /** Region1 ends**/

BEGIN /** Region2 Starts **/
....
....
....
END /** Region2 Ends **/
StackUser
  • 5,370
  • 2
  • 24
  • 44