Giving conditions in SQL SELECT statement

Question

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.