t sql "select case" vs "if ... else" and explaination about "begin"

sql, sql-server, stored-procedures, switch-statement, t-sql

Solution

For a single IF Statement

IF (Some Condition)     --<-- If condition is true control will get inside the 
 BEGIN                     -- BEGIN ..END Block and execute the Code inisde
   /* Your Code Here*/
 END

All the single IF statements will check the Conditions Independently.

ONE IF with ONE ELSE

IF (Some Condition)     --<-- If condition is true control will get inside the 
 BEGIN                     -- BEGIN ..END Block and execute the Code inisde
   /* Your Code Here*/     -- IF not true control will jump to Else block
 END
ELSE       --<-- You dont mention any condition here
 BEGIN                    
   /* Your Code Here*/
 END

Only One block of code will execute IF true then 1st block Otherwsie ELSE block of code.

Multiple IFs and ELSE

IF (Some Condition)     --<--1) If condition is true control will get inside the 
  BEGIN                     -- BEGIN ..END Block and execute the Code inisde
    /* Your Code Here*/     -- IF not true control will check next ELSE IF Blocl
  END
ELSE IF (Some Condition)       --<--2) This Condition will be checked
  BEGIN                    
      /* Your Code Here*/
  END
ELSE IF (Some Condition)       --<--3) This Condition will be checked
  BEGIN                    
     /* Your Code Here*/
  END
ELSE                --<-- No condition is given here Executes if non of
  BEGIN                --the previous IFs were true just like a Default value   
     /* Your Code Here*/
  END

Only the very 1st block of code will be executed WHERE IF Condition is true rest will be ignored.

BEGIN ..END Block

After any IF, ELSE IF or ELSE if you are Executing more then one Statement you MUST wrap them in a `BEGIN..END` block. not necessary if you are executing only one statement BUT it is a good practice to always use BEGIN END block makes easier to read your code.

Your Procedure

I have taken out ELSE statements to make every IF Statement check the given Conditions Independently now you have some Idea how to deal with IF and ELSEs so give it a go yourself as I dont know exactly what logic you are trying to apply here.

CREATE PROCEDURE [dbo].[myStored]
(
@myPar1 INT,
@myPar2 SMALLDATETIME
)

AS

BEGIN
  SET NOCOUNT ON

  IF EXISTS (SELECT 1 FROM myTable1 WHERE myPar1 = @myPar1
            AND myPar2 = @myPar2)
    BEGIN
      DELETE FROM  myTable1  
      WHERE myPar1 = @myPar1 AND myPar2 = @myPar2
    END


    IF EXISTS (SELECT 1 FROM myTable2 WHERE myPar2 = @myPar2)
      BEGIN
         INSERT INTO  myTable1(myField1, myField2, myField3, myField4)
         VALUES(@myPar1, @myPar2, '', 1)
      END    


      IF EXISTS (SELECT 1 FROM myTable3  WHERE myPar2 = @myPar2)
        BEGIN
           INSERT INTO  myTable1(myField1, myField2, myField3, myField4)
           VALUES(@myPar1, @myPar2, '', 1)
        END
END

Problem

Original source