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

Question

I have few experiences with t sql and I have to write a stored.

This is my stored:

USE myDatabase
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

  ELSE 

    IF EXISTS (
              SELECT 
              1 

              FROM 
              myTable2 

              WHERE 
              myPar2 = @myPar2
              )

      BEGIN
        INSERT INTO  
        myTable1
        (myField1, myField2, myField3, myField4)

        VALUES
        (@myPar1, @myPar2, '', 1)
      END    

    ELSE

      IF EXISTS (
                SELECT 
                1 

                FROM 
                myTable3 

                WHERE 
                myPar2 = @myPar2
                )

        BEGIN
          INSERT INTO  
          myTable1
          (myField1, myField2, myField3, myField4)

          VALUES
          (@myPar1, @myPar2, '', 1)
        END
END

And these are my questions:

1 - Are there macroscopic errors?

2 - Someone suggest to use "SELECT CASE" someone else to use "IF ... ELSE", what's the difference? And what is the best option for my stored?

3 - I'm not sure about the use of the "BEGIN ... END" statement, in particular in combination with "IF ... ELSE" statement. What does it mean? Is it necessary to put "BEGIN ... END" inside the "IF ... ELSE" statement? Also for executing a single instruction?

Solution 1

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

Solution 2

1. I don't see any macroscopic error
2. IF ELSE statement are the one to use in your case as your insert or delete data depending on the result of your IF clause. The SELECT CASE expression is useful to get a result expression depending on data in your SELECT statement but not to apply an algorithm depending on data result.
3. See the BEGIN END statement like the curly brackets in code { code }. It is not mandatory to put the BEGIN END statement in T-SQL. In my opinion it's better to use it because it clearly shows where your algorithm starts and ends. Moreover, if someone has to work on your code in the future it'll be more understandable with BEGIN END and it'll be easier for him to see the logic behind your code.

Solution 3

1. There are no errors in this script.

2. Case statement is for expression evaluation and not for statement execution. Hence, cannot be used in this current requirement. For more details about case statement look at http://msdn.microsoft.com/en-us/library/ms181765.aspx

3. Usually, a statement can be single or a compound. Compound ones are a combination of statements. For an IF condition, one can specify a single or compound statement and SQL server chose to group it inside a BEGIN .. END (unlike few other databases/programming languages). This is same for ELSE. Hence, IF should be followed by BEGIN...END and ELSE should be followed by BEGIN...END. For more details please refer to http://msdn.microsoft.com/en-us/library/ms182717(v=sql.120).aspx