Giving conditions in SQL SELECT statement

Question

Hi All, I have to run an SQL query based on conditions. There are 2 AND conditions that needs to be executed only if the if conditions for them are satisfied. Can we use CASE statement here. If so how? Or are there any other methods??

SELECT * FROM MyTable
WHERE col1=@val1

if condition1 here
AND col2 = @val2
end if

if condition2 here
AND col3 = @val3
end if

Can anyone help me on this please. I am using sql server 2005.

Answer 1

Have them in your query, like so:

SELECT * FROM MyTable
WHERE col1=@val1
And (Not Condition1 Or col2 = @val2)
And (Not Condition2 Or col3 = @val3)

So, if Not Not Condition1 (meaning: Condition1 is true) Then col2 (must) = @val2.

(I changed the 2nd conditional logic to AND col3 = @val3, because you had repeated AND col2 = @val2)

Edit in response to comment: Give me an example of the criteria for condition1. Explicit if syntax is not used within queries in t-sql. Let's say the "previous orders" has to be less than 20 for the first criteria to matter, and . . . the second criteria to matters if there's no first name. It'd be:

SELECT * FROM MyTable
WHERE col1=@val1
And (MyTable.OrderCount > 19 Or col2 = @val2)
And ((Not MyTable.FirstName Is Null) Or col3 = @val3)

Don't think of it in terms of this "this criteria matters if this situation is true". All the factors that matter go into the where clause. You have to understand the Or/And/Not and orders of operation. This takes practice to get good. If you have to think through it for a few minutes, don't worry, so do the rest of us sometimes.

Answer 2

I'm assuming you meant to have two different conditions and col/val pairs, not the same one as in your post.

If "condition" is something that exists inside the context of the query (i.e. not an external factor), then you can do something like this:

SELECT * FROM MyTable
WHERE col1 = @val1
  AND (NOT condition1 OR (condition1 AND col2 = @val2))
  AND (NOT condition2 OR (condition2 AND col3 = @val3))

Edit: OK, so the "conditionX AND" is redundant, but I think it's a good idea for documentation purposes as it makes it explicit the intent of the construct.

Answer 3

Assuming the conditions can be written as a SQL expressions:

SELECT * FROM MyTable
WHERE col1=@val1 AND
(NOT(condition_1) OR col2 = @val2) AND
(NOT(condition_2) OR col3 = @val3) 

Answer 4

SELECT * FROM MyTable
WHERE col1=@val1 AND ((condition1 AND col2=@val2) OR (condition2 AND col2=@val2))

So if condition1 is true it will evaluate the col2=@val2, if condition1 is false it will go to OR (condition2, and do the same.

Answer 5

Without using dynamic SQL, the best approach is:

IF condition
  BEGIN
     SELECT x.* 
       FROM MyTable x
      WHERE x.col1 = @val1
        AND x.col2 = @val2
  END
ELSE IF condition
  BEGIN
     SELECT y.* 
       FROM MyTable y
      WHERE y.col1 = @val1
        AND y.col3 = @val3    
  END
ELSE
  BEGIN
     SELECT z.* 
       FROM MyTable z
      WHERE z.col1 = @val1
        AND z.col4 = @val4
  END

ORs and/or conditional logic will impact both performance, readability, and maintainability.

The benefit to the dynamic SQL approach is that it would perform as well as the non-dynamic counterpart I first posted, but only be one SQL statement so there's less concern of someone tweaking one of the conditions to return different values/data/columns:

DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT t.*
                  FROM MYTABLE t
                 WHERE t.col1 = @param1 '

    SET @SQL = @SQL + CASE 
                        WHEN condition1 THEN ' AND x.col2 = @param2 '
                        WHEN condition2 THEN ' AND x.col3 = @param3 '
                        ELSE  ' AND x.col4 = @param4 '
                      END

BEGIN

  EXEC SP_EXECUTESQL @SQL 
                     'param1 [data type], param2 [data type], param3 [data type], param4 [data type]',
                     @param1, @param2, @param3, @param4

END

For more info on Dynamic SQL in SQL Server, I recommend reading this article. There is a risk of SQL injection if not using parameterized queries, and be aware that using EXEC does not cache the query plan while using EXEC SP_EXECUTESQL does cache the query plan.

Answer 6

Two very important pages you need to read are:

The following covers SQL 2000 and SQL 2005 http://www.sommarskog.se/dyn-search-2005.html

This one is specific for SQL 2008. http://www.sommarskog.se/dyn-search-2008.html

Answer 7

Man people has no clue about SQL these days... Amazing...

SELECT * 
FROM MyTable
WHERE col1=@val1 
and case when condition1 then col2 = @val2 else 1=1 end 
and case when condition2 then  col3 = @val3 else 1=1 end

Answer 8

If the columns are not nullable (and don't criticise, try it)

SELECT
   *
FROM
   MyTable
WHERE
   col1 = @val1 AND
   col2 = ISNULL(@val2, col2) AND
   col3 = ISNULL(@val2, col3)

Otherwise (Statement level recompilation makes it acceptable on SQL Server 2005+)

if condition1 here
  SELECT * FROM MyTable
  WHERE col1=@val1AND col2 = @val2


if condition2 here
  SELECT * FROM MyTable
  WHERE col1=@val1AND col3 = @val3

else
 SELECT * FROM MyTable
 WHERE col1=@val1

Or use the other solutions offered