Home > Net >  Can I use the IF ELSE SQL statement without BEGIN and END?
Can I use the IF ELSE SQL statement without BEGIN and END?

Time:01-11

I notice that it is not necessary to use the BEGIN and END in a IF ELSE T-SQL Statement. So why we need it?

DECLARE @abc  int = 1
      , @test int = 3

IF @abc = 1 

   IF @test = 3
   
      SELECT 34

   ELSE 
  
      SELECT 4444

ELSE

   IF @test = 3
   
      SELECT 3

   ELSE 
  
      SELECT 4

I get the right results.

CodePudding user response:

The example is somewhat contrived. What if each branch contained two statements, eg SELECT 34; SELECT 42;? Or worse, SELECT 34; DELETE ...; ?

IF @abc = 1 

   IF @test = 3
   
   SELECT 34
   DELETE SomeTable
   WHERE ID>34

   ELSE 
  
   SELECT 4444

ELSE

   IF @test = 3
   
   SELECT 3

   ELSE 
  
   SELECT 4

The query compiler sees this:

IF @abc = 1 
BEGIN
   IF @test = 3
   BEGIN
       SELECT 34
   END
END

DELETE SomeTable
WHERE ID>34

ELSE...

Fortunately, ELSE result in a syntax error. Without ELSE the DELETE statement will get executed unconditionally.

In this case BEGIN and END are needed to define the block :

IF @abc = 1 
BEGIN
   IF @test = 3
   BEGIN
       SELECT 34
       DELETE SomeTable
       WHERE ID>34
   END
END

  • Related