Conditional WHERE clause in SQL Server

Question

I am creating a SQL query in which I need a conditional where clause     It should be something like this   SELECT      DateAppr      TimeAppr      TAT      LaserLTR      Permit      LtrPrinter      JobName      JobNumber      JobDesc      ActQty       ActQty-LtrPrinted  AS L       ActQty-QtyInserted  AS M        ActQty-LtrPrinted - ActQty-QtyInserted   AS N FROM       test   dbo   MM  WHERE      DateDropped   0             --This is where i need the conditional clause      AND CASE             WHEN  JobsOnHold   1 THEN DateAppr  gt    0             ELSE  DateAppr    0         END   The above query is not working  Is this not the correct syntax or is there another way to do this that I don t know    I don t want to use dynamic SQL  so is there any other way or do I have to use a workaround like using if else and using the same query with different where clauses

User · Answer

Try this one -  WHERE DateDropped   0     AND            ISNULL  JobsOnHold  0    1 AND DateAppr  gt   0           OR           ISNULL  JobsOnHold  0     1 AND DateAppr    0

User · Answer

This seemed easier to think about where either of two parameters could be passed into a stored procedure  It seems to work  SELECT    FROM x  WHERE CONDITION1 AND    pol IS NOT NULL AND x PolicyNo    pol  OR   st IS NOT NULL AND x State    st   AND OTHERCONDITIONS

User · Answer

To answer the underlying question of how to use a CASE expression in the WHERE clause    First remember that the value of a CASE expression has to have a normal data type value  not a boolean value   It has to be a varchar  or an int  or something   It s the same reason you can t say SELECT Name  76   Age FROM       and expect to get  Frank   FALSE in the result set   Additionally  all expressions in a WHERE clause need to have a boolean value   They can t have a value of a varchar or an int   You can t say WHERE Name  or WHERE  Frank     You have to use a comparison operator to make it a boolean expression  so WHERE Name    Frank    That means that the CASE expression must be on one side of a boolean expression   You have to compare the CASE expression to something   It can t stand by itself   Here   WHERE      DateDropped   0     AND CASE             WHEN  JobsOnHold    1 AND DateAppr  gt   0 THEN  True              WHEN DateAppr    0 THEN  True              ELSE  False          END    True    Notice how in the end the CASE expression on the left will turn the boolean expression into either  True     True  or  False     True    Note that there s nothing special about  False  and  True    You can use 0 and 1 if you d rather  too   You can typically rewrite the CASE expression into boolean expressions we re more familiar with  and that s generally better for performance   However  sometimes is easier or more maintainable to use an existing expression than it is to convert the logic

User · Answer

Try this   SELECT      DateAppr      TimeAppr      TAT      LaserLTR      Permit      LtrPrinter      JobName      JobNumber      JobDesc      ActQty       ActQty-LtrPrinted  AS L       ActQty-QtyInserted  AS M        ActQty-LtrPrinted - ActQty-QtyInserted   AS N FROM       test   dbo   MM  WHERE      DateDropped   0     AND        ISNULL  JobsOnHold  0    1 AND DateAppr  gt   0       OR       ISNULL  JobsOnHold  0     1 AND DateAppr    0          You can read more about conditional WHERE here

User · Answer

The problem with your query is that in CASE expressions  the THEN and ELSE parts have to have an expression that evaluates to a number or a varchar or any other datatype but not to a boolean value   You just need to use boolean logic  or rather the ternary logic that SQL uses  and rewrite it   WHERE      DateDropped   0 AND    JobsOnHold   1 AND DateAppr  gt   0     OR   JobsOnHold  lt  gt  1 OR  JobsOnHold IS NULL  AND DateAppr  lt  gt  0

User · Answer

Often when you use conditional WHERE clauses you end upp with a vastly inefficient query  which is noticeable for large datasets where indexes are used  A great way to optimize the query for different values of your parameter is to make a different execution plan for each value of the parameter  You can achieve this using OPTION  RECOMPILE    In this example it would probably not make much difference  but say the condition should only be used in one of two cases  then you could notice a big impact   In this example   WHERE      DateDropped   0     AND        ISNULL  JobsOnHold  0    1 AND DateAppr  gt   0       OR       ISNULL  JobsOnHold  0   lt  gt  1 AND DateAppr  lt  gt  0        OPTION  RECOMPILE    Source Parameter Sniffing  Embedding  and the RECOMPILE Options

[sql] Conditional WHERE clause in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to where-clause