What is the benefit of using BEGIN and END in SQL?

It allows you to group statements and to be executed as group.


Declare @p1 int
set @p1 = 1

IF @p1 = 1
      print (@p1)
print(@p1*2)

and also try

Declare @p1 int
set @p1 = 1

IF @p1 = 1
BEGIN
      print (@p1)
      print(@p1*2)
END

It is there to delimit the block of code. If you have an if/else statement where there if and else blocks only have a single line of code you don't need the begin and end but I personally put them in to make it clear that the block is there.

Please review following links for more details:

http://www.fianga.com/sql/beginend.htm
http://technet.microsoft.com/en-us/library/ms190487.aspx

If you need to use it, then you need to use it. It is not a benefit. Begin-End is used to logically block several statements together, such as shown in comments above.

When you don't need it, then we can talk about "benefit".  In this case, the "benefit" is purely decorational, and personally I don't believe they add any real value to a statement batch.

Hi,

Basically it marks a Batch. A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.
Details of a batch: http://msdn.microsoft.com/en-us/library/ms175502.aspx

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, or statement block.
Details: http://msdn.microsoft.com/en-us/library/ms190487.aspx

thanks