CODEWITHSUNDEEP
sqlsql-server
Sylar
Sylar

Reputation: 12092

Using IF Statement inside a Where Clause in SQL Server

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?

Upvotes: 0

Views: 64

Answers (4)

Jeremy Giaco
Jeremy Giaco

Reputation: 402

--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)

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23837

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

Upvotes: 0

Error_2646
Error_2646

Reputation: 3849

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)
    )
";

Upvotes: 2

juergen d
juergen d

Reputation: 204904

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

Upvotes: 3

Related Questions