CODEWITHSUNDEEP
sqlsql-server
whytheq
whytheq

Reputation: 35577

Transferring IF into WHERE clause

The following works

IF OBJECT_ID('TEMPDB..#Users') IS NOT NULL 
BEGIN 
    DROP TABLE #Users 
END;

CREATE TABLE #Users(UserAccountKey INT);

IF @Period IN ('Overall', 'Week1', 'Week2', 'Week3', 'Week4') 
BEGIN
   INSERT INTO #Users
      SELECT UserAccountKey
      FROM XXX c
      WHERE c.Name NOT IN ('X', 'Y', 'Z', 'J', 'Q')
      GROUP BY UserAccountKey
END
ELSE
BEGIN
   INSERT INTO #Users
      SELECT UserAccountKey
      FROM XXX c
      WHERE c.Name NOT IN ('X', 'Y')
      GROUP BY UserAccountKey;
END;

Is it possible to shorten this to a single sql statement? Can the IF condition be transferred to the WHERE clause?

Upvotes: 2

Views: 104

Answers (4)

Allan
Allan

Reputation: 17429

The best way to handle this is to translate the if to a single condition. I believe this will provide the same result:

   INSERT INTO #Users
      SELECT UserAccountKey
      FROM XXX c
      WHERE (@Period NOT IN ('Overall', 'Week1', 'Week2', 'Week3', 'Week4')
             and c.Name NOT IN ('X', 'Y'))
         or (@Period IN ('Overall', 'Week1', 'Week2', 'Week3', 'Week4')
            and c.Name NOT IN ('X', 'Y', 'Z', 'J', 'Q'))
      GROUP BY UserAccountKey

This has been bothering me, because I think it should be able to be simplified, but I couldn't figure out how. My original answer was the same as @Pirion's, but I changed it because I realized it would not be equivalent to the original script. Below is a simplified script that I think provides the same functionality as my earlier answer:

   INSERT INTO #Users
      SELECT UserAccountKey
      FROM XXX c
      WHERE not (c.Name IN ('X', 'Y')
                 or (@Period IN ('Overall', 'Week1', 'Week2', 'Week3', 'Week4')
                     and c.Name IN ('Z', 'J', 'Q'))
      GROUP BY UserAccountKey

Upvotes: 2

Pirion
Pirion

Reputation: 519

Can simplfy a litle more:

  INSERT INTO #Users
  SELECT UserAccountKey
  FROM XXX c
  WHERE c.Name NOT IN ('X', 'Y')
        OR (@Period IN ('Overall', 'Week1', 'Week2', 'Week3', 'Week4')
             AND c.Name NOT IN ('Z', 'J', 'Q'))
  GROUP BY UserAccountKey

Upvotes: 3

Mathias F
Mathias F

Reputation: 15911

INSERT INTO #Users
SELECT userAccountKey
FROM XXX c
WHERE ( c.Name NOT IN ('X','Y','Z','J','Q')
AND @Period IN ('Overall','Week1','Week2','Week3','Week4'))

OR
(
c.Name NOT IN ('X','Y')
AND @Period NOT IN ('Overall','Week1','Week2','Week3','Week4')
)
GROUP BY UserAccountKey

Upvotes: 1

Melanie
Melanie

Reputation: 3111

INSERT INTO #Users
SELECT userAccountKey
FROM XXX c
WHERE c.Name NOT IN ('X','Y','Z','J','Q')
AND @Period IN ('Overall','Week1','Week2','Week3','Week4')
GROUP BY UserAccountKey

INSERT INTO #Users
SELECT UserAccountKey
FROM xxx C
WHERE c.Name NOT IN ('X','Y')
AND @Period NOT IN ('Overall','Week1','Week2','Week3','Week4')
GROUP BY UserAccountKey

Upvotes: 0

Related Questions