Using IF Statement inside a Where Clause in SQL Server

Question

I have two params that are passed in a function which passed down to the SQL string. Variables can be null or has a value (int). If x is not null, use "this" column else use "that" column. I'm using SQL Server.

// Inside a function with two variables passed, x and y
$sql = "
  SELECT
    [...]
  FROM
    [...]
  WHERE
    [...]
    AND [...]
    -- This is the tricky part
    AND
    --- if $x is not null, use foo column else use bar column
    IF (x, R.column = 2, R.another_column = 3)
    [...]
";

Is this possible to select a column based on the value of the variable passed in?

I think you just looking for a case statement.

Error_2646
– Error_2646

2018-07-19 19:48:48 +00:00
Commented Jul 19, 2018 at 19:48 — Error_2646
– Error_2646, Commented Jul 19, 2018 at 19:48

juergen d · Accepted Answer · 2018-07-19 19:49:39Z

3

AND
(
   ($x is not null and R.column = 2) OR
   ($x is null and R.another_column = 3)
)

answered Jul 19, 2018 at 19:49

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Error_2646 · Accepted Answer · 2018-07-19 19:51:28Z

2

Unless I'm misunderstanding, you just need logic, or a case statement would work.

$sql = "
  SELECT
    [...]
  FROM
    [...]
  WHERE
    [...]
  AND
    [...]
  -- This is the tricky part
  AND
    --- if $x is not null, use foo column else use bar column
    -- IF (x, R.column = 2, R.another_column = 3)
    (
    (X IS NULL AND R.column = 2)
    OR
    (X IS NOT NULL AND R.another_column = 3)
    )
";

answered Jul 19, 2018 at 19:51

Error_2646

3,8991 gold badge17 silver badges26 bronze badges

Comments

Cetin Basoz · Accepted Answer · 2018-07-19 19:53:19Z

0

AND
    --- if $x is not null, use foo column else use bar column
    case
      when x is NULL then R.another_column = 3 
      else R.column = 2 
    end

answered Jul 19, 2018 at 19:53

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Comments

Jeremy Giaco · Accepted Answer · 2018-07-19 19:54:12Z

0

--in SQL Server...
DECLARE @x INT = 3;

SELECT *
FROM sys.schemas
WHERE (@x IS NULL AND schema_id = 2)
        OR (@x IS NOT NULL and schema_id = 3)
--optional depending on how much you execute this query, it may help
OPTION(RECOMPILE)

answered Jul 19, 2018 at 19:54

Jeremy Giaco

4125 silver badges6 bronze badges

Collectives™ on Stack Overflow

Using IF Statement inside a Where Clause in SQL Server

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related