Providing conditions in an SQL SELECT statement

Question

I need to run a conditional SQL query. There are 2 And conditions that must be met only if the conditions for them are met. Can we use the CASE statement here. If so, how? Or are there 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 someone help me with this please. I am using SQL Server 2005.

Answer 1

Have them in your request, for example:

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

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

(I changed the second conditional logic to AND col3 = @ val3 because you repeated AND col2 = @ val2)

: 1. , t-sql. , " " 20 , , ., , . :

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

" , ". , , where. Or/And/Not . , . , , .

Answer 2

, SQL:

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

Answer 3

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

, 1 , col2 = @val2, 1 , OR (2 .

Answer 4

, :

SQL 2000 SQL 2005 http://www.sommarskog.se/dyn-search-2005.html

SQL 2008. http://www.sommarskog.se/dyn-search-2008.html

Answer 5

( , )

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

( 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

Answer 6

, col/val, , .

If the “condition” is something that exists inside the request context (i.e. not an external factor), 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 “condition X AND” is redundant, but I think this is a good idea for documentation purposes, as it makes the intent of the construct explicit.

Answer 7

Human people have no idea 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