Conditional Where clause possible?

Question

Is it possible to use a conditional statements in a where clause?

 

IE:  I have 3 paramaters that may or may not be filled.

 

I would like to do something along the lines of...

 

 

Select * From (tables)

WHERE

    If @param1 has value

          Begin

                 'run this where statement

   if @Param2 has value

               'add this to the where clause

if @param3 has value

              'add this to the where cluase

 

 

Answer 1

Dynamic Search Conditions in T-SQL

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

 

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

 

 

AMB

Answer 2

thanks but I can't get to those websites...

Our company websense filters that out as "personal"

 

 

Answer 3

Sometimes you can get away with something like:

 

Select * From (tables)

WHERE

    (Field1 = @param1 OR @param1 IS NULL) AND

   (Field 2 = @param2 OR @param2 IS NULL) AND      ...

 

 

Answer 4

Then tell your IT department that they are actually work related and why and ask them to allow access to them.

 

Simple as that.

 

Answer 5

Mainiac007,

 

You can't do conditional code in T-SQL (unlike PL/SQL). You can, however, do this:

 

Select * From (tables)

where

col1 = coalesce(@param1, col1)

and col2 = coalesce(@param2, col2)

and ...

 

Ron

Answer 6

 

yes you can have conditional where clauses in t-sql here is a simple example

 

 

select * from mytable
where
mycol =  case when @i = 1 then 1 else 2 end

 

you can find many examples on google.

 

Answer 7

You can user OR logic to achive your goal...

For example we have a parameter. If this parameter is true we want to include stocks under min stock number. otherwise select all stock records

 

DECLARE @UnderMin BIT
SET @UnderMin = 0

select * from Stocks
where ( @UnderMin = 1 or @UnderMin is null OR OnHand >= MinStock )

In same way you can ignore parameters that have null values...

You can select all users if there is no user id specified.

select * from users 
where (userid = @UserId or @UserId is null )

 

Answer 8

yes you can have conditional where clauses in t-sql here is a simple example

 

 

select * from mytable
where
mycol =  case when @i = 1 then 1 else 2 end

 

you can find many examples on google.

 

 

I do not suggest that...

Answer 9

Is it possible to use a conditional statements in a where clause?

 

IE:  I have 3 paramaters that may or may not be filled.

 

I would like to do something along the lines of...

 

 

Select * From (tables)

WHERE

    If @param1 has value

          Begin

                 'run this where statement

   if @Param2 has value

               'add this to the where clause

if @param3 has value

              'add this to the where cluase

 

 

you can use CASE cllause in where condition ,

like case when  @param1 has value then first statement when @param2 has value then second condition...

---

Paresh Prajapati
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj