SQL Server BEGIN

Question

First, I have seen this post SO on the Topic but I never knew when you really need and don't need BEGIN END.

Take this example

  If (@pInsLName <> 'null')
    Select @pInsLNameact = Cast (@pInsLName As VarChar(60)) 
  Else
    Select @pInsLNameact = Null

Or

  If (@pInsLName <> 'null')
    Begin
      Select @pInsLNameact = Cast (@pInsLName As VarChar(60))    
    End
  Else
    Begin
      Select @pInsLNameact = Null
    End

In the example above is there any benefit to using versus not using BEGIN and END statements?

`BEGIN` and `END` wrap *blocks* of code. The top snippet in your post is acceptable because the statements within your `IF` and `ELSE` only contain a single line, however as soon as you have two or more, you'll need `BEGIN...END`. — Tyler Roper, Jan 09 '17 at 15:28

maxshuty · Answer 1 · 2017-01-09T15:31:53.583

1

I like the example below from MSDN:

If you did not include the BEGIN and END both of the ROLLBACK TRANSACTION statements and both of the PRINT statements would execute.

USE AdventureWorks2012;  
GO  
BEGIN TRANSACTION;  
GO  
IF @@TRANCOUNT = 0  
BEGIN  
    SELECT FirstName, MiddleName   
    FROM Person.Person WHERE LastName = 'Adams';  
    ROLLBACK TRANSACTION;  
    PRINT N'Rolling back the transaction two times would cause an error.';  
END;  
ROLLBACK TRANSACTION;  
PRINT N'Rolled back the transaction.';  
GO  
/*  
Rolled back the transaction.  
*/

edited Jan 09 '17 at 15:31

answered Jan 09 '17 at 15:30

maxshuty

9,708
13
64
77

1

MSDN, truly poor example, since there is nothing in the code to rollback – Charles Bretana Jan 09 '17 at 15:35
A transaction is still created. – gmiley Jan 09 '17 at 15:38

score 1 · Accepted Answer · edited May 23 '17 at 12:34

1

I think the question/answer you linked to explains it well enough. There is also this entry. Basically BEGIN and END signify a block of statements. In the case of an IF it groups the statements together so that they all execute as part of the condition defined in the IF or ELSE portions of the statement.

edited May 23 '17 at 12:34

Community

1
1

answered Jan 09 '17 at 15:39

gmiley

6,531
1
13
25

the post you shared finally got it to click in my thick head. From the post "BEGIN and END are just like { and } in C/++/#, Java, etc." I needed the comment about the { }. – Neo Jan 09 '17 at 15:41

SQL Server BEGIN

2 Answers2