Giving conditions in SQL SELECT statement

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
WH开发者_如何转开发ERE 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.

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.

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) 

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.

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

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

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.

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