SQL Server 2008 - Conditional Query

Conditional WHERE Clauses in SQL Server 2008

I've done something like this in the past:

SELECT
  .....
FROM
  dbo.SOMETABLE AS T
WHERE
  (T.memberType = @memberType OR @memberType = -1)
  AND (T.color = @color OR @color = -1)
  AND (T.preference = @preference OR @preference = -1)
  AND (T.groupNumber = @groupNumber OR @groupNumber = -1)
  AND (T.departmentNumber = @departmentNumber OR @departmentNumber = -1)

Generally, however, my parameters that I don't care about are NULL. Then the query becomes:

SELECT
    .....
  FROM
    dbo.SOMETABLE AS T
  WHERE
    (T.memberType = @memberType OR @memberType IS NULL)
    AND (T.color = @color OR @color IS NULL)
    AND (T.preference = @preference OR @preference IS NULL)
    AND (T.groupNumber = @groupNumber OR @groupNumber IS NULL)
    AND (T.departmentNumber = @departmentNumber OR @departmentNumber IS NULL)

Simplyfing conditional in one single query in SQL Server 2008

Move your IF condition into a CASE, then nest your other CASE inside of it.

SELECT
  d.DeptId
 ,Description
FROM
  Dept AS d
  LEFT JOIN
    DeptOrder AS o
      ON
      p.DeptId = o.DeptId
WHERE
  d.DeptId IN
    ( 3, 7, 9, 10, 17, 20 )
ORDER BY
  CASE
    WHEN @OrderByDescription = 1 THEN Description
    ELSE CASE
           WHEN o.[Order] IS NULL THEN 1
           ELSE 0
         END
  END
 ,o.[Order];

T-SQL Conditional Query

You should read Dynamic Search Conditions in T‑SQL by Erland Sommarskog.

If you use SQL Server 2008 or later, then use OPTION(RECOMPILE) and write the query like this:

SELECT * 
FROM Customer 
WHERE
    (Name = @SearchValue OR @QueryAll='true')
OPTION (RECOMPILE);

I usually pass NULL for @SearchValue to indicate that this parameter should be ignored, rather than using separate parameter @QueryAll. In this convention the query becomes this:

SELECT * 
FROM Customer 
WHERE
    (Name = @SearchValue OR @SearchValue IS NULL)
OPTION (RECOMPILE);

Edit

For details see the link above. In short, OPTION(RECOMPILE) instructs SQL Server to recompile execution plan of the query every time it is run and SQL Server will not cache the generated plan. Recompilation also means that values of any variables are effectively inlined into the query and optimizer knows them.

So, if @SearchValue is NULL, optimizer is smart enough to generate the plan as if the query was this:

SELECT * 
FROM Customer 

If @SearchValue has a non-NULL value 'abc', optimizer is smart enough to generate the plan as if the query was this:

SELECT * 
FROM Customer 
WHERE (Name = 'abc')

The obvious drawback of OPTION(RECOMPILE) is added overhead for recompilation (usually around few hundred milliseconds), which can be significant if you run the query very often.

SQL Server 2008 - Case / If statements in SELECT Clause

Just a note here that you may actually be better off having 3 separate SELECTS for reasons of optimization. If you have one single SELECT then the generated plan will have to project all columns col1, col2, col3, col7, col8 etc, although, depending on the value of the runtime @var, only some are needed. This may result in plans that do unnecessary clustered index lookups because the non-clustered index Doesn't cover all columns projected by the SELECT.

On the other hand 3 separate SELECTS, each projecting the needed columns only may benefit from non-clustered indexes that cover just your projected column in each case.

Of course this depends on the actual schema of your data model and the exact queries, but this is just a heads up so you don't bring the imperative thinking mind frame of procedural programming to the declarative world of SQL.

SQL query update conditional

Try this:

update a
set a.amount=0
from aaa a inner join bbb b 
on a.channel_id=b.channel_id
where ((a.channel_id=b.channel_id and a.type_id=b.type_id and b.type_id IS NOT NULL)
OR (a.channel_id=b.channel_id and b.type_id IS NULL))

conditional query

I think this removes the need for the if/else but beware that this may not be efficient

 Select mytable.field1, mytable.field2, mytable.field3, mytable.field4,mytable.field5
    from mytable
    Where mytable.field6 is null
    and mytable.field1 is not null
    and (mytable.state = @paramstate  or @paramstate = 'All')

---

Related Topics