Conditional Where Clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional Where Clause

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-24 : 07:53:49

Hi All,

I had one dynamic SP, which i hv converted to Static. Here i m stuck at one situation. (I tried this to overcome the prob)

select a,c
from x
where a = 1
and (case
when @b <> 'ALL' then b = @b
end )

it throwing an error.

can anybody tell me, how to apply such conditional where clause. i wants to apply "AND ... " criteria only when i pass @b other than "ALL".

hopes i hv explained all,

thanks in advance,

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-24 : 08:00:56

[code]Where
a = 1 and
b = (case when @b <> 'ALL' then @b else b end)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-24 : 08:42:12

You don't need a CASE. Just simple logic:

where a=1 and (@b='ALL' or b=@b)

- Jeff
http://weblogs.sqlteam.com/JeffS

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-24 : 09:37:38

quote:
Originally posted by harsh_athalye

Where
a = 1 and
b = (case when @b <> 'ALL' then @b else b end)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

i changed it to ...

Where a = @a
And (@b <> 'ALL' And b = @b)

Mahesh

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-24 : 10:10:56

quote:
Originally posted by mahesh_bote

quote:
Originally posted by harsh_athalye

Where
a = 1 and
b = (case when @b <> 'ALL' then @b else b end)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

i changed it to ...

Where a = @a
And (@b <> 'ALL' And b = @b)

Mahesh

With that, no data at all will ever be returned if @b = 'ALL' ... I doubt that's what you want.

- Jeff
http://weblogs.sqlteam.com/JeffS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-24 : 10:13:45

someone needs a quick leson in boolean algebra.
so how about it jeff? how dare you give such advice?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-25 : 12:22:41

quote:
Originally posted by jsmith8858

quote:
Originally posted by mahesh_bote

quote:
Originally posted by harsh_athalye

Where
a = 1 and
b = (case when @b <> 'ALL' then @b else b end)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

i changed it to ...

Where a = @a
And (@b <> 'ALL' And b = @b)

Mahesh

With that, no data at all will ever be returned if @b = 'ALL' ... I doubt that's what you want.

- Jeff
http://weblogs.sqlteam.com/JeffS

Hi Jeff,

Is that so ???

As per you, if i pass param value as 'ALL', it will not return anything? Please clear, its very imp.

Thanks,

Mahesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-26 : 03:13:23

<<
As per you, if i pass param value as 'ALL', it will not return anything? Please clear, its very imp.
>>

Yes you wont get records
Why dont you test with some sample data?

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-26 : 03:29:17

just sound it out in your head, you should mutter something like this:

"select some stuff where @b is not equal to 'all' and b = @b"

so, if @b = 'all', what do you get?

www.elsasoft.org

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-28 : 07:43:42

Hi All,

I think, still the situation is not clear, what i meantioned. suppose:

If @B <> 'ALL'
Begin
Select .... From ... Where A = 1 And
B = @B
End
Else
Begin
Select .... From ... Where A = 1

End

Means i wants to apply this criteria only when i pass the value to parameter @B other than 'ALL'. and if it is 'ALL' don't apply the highlighted criteria to the where clause.

So how to achive this in static sql?

I applied my and even other solutions to my qry to get the op. when i pass other than 'ALL' it retrives the data. but not in case of non - 'ALL'

Or shall i have to pass all the values from drop down, from business logic, to where clause?

thanks,
Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-28 : 08:21:32

Have you tried Jeff's solution or mine? May be you should post some sample data and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-28 : 08:24:33

select a,c
from x
where a = 1
and @b in ('all', b)

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-28 : 08:40:33

this is my sample data
Num T_Name
1 A
2 B
3 C
4 D

'ALL' is not present in T_Name

If is pass 'ALL' to @T_Name it should retrive all the rows, that means
Num T_Name
1 A
2 B
3 C
4 D

so for this situation, i applies this condition:
...
where Num = 2 And (@T_Name <> 'ALL' And T_Name = @T_Name)
...

the above situation is, when i pass other than 'ALL' to param @T_Name

hopes that is all,
thanks,

Mahesh

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-28 : 08:49:26

I believe you have tendancy to ignore solutions given here.

Why don't you try the solutions firsthand before posting here?

-- Prepare sample data
declare @t table
(
	Num int,
	T_Name varchar(10)
)
insert @t
select 1, 'A' union all
select 2, 'B' union all
select 3, 'C' union all
select 4, 'D'

declare @t_name varchar(10)

------------------------------------
-- using Jeff's solution
------------------------------------

-- For specific value
set @t_name = 'A'

select * from @t
where (@t_name = 'ALL' or t_name = @t_name)

-- For all values
set @t_name = 'ALL'

select * from @t
where (@t_name = 'ALL' or t_name = @t_name)

------------------------------------
-- using Harsh's solution
------------------------------------

-- For specific value
set @t_name = 'A'

select * from @t
where t_name = (case when @t_name <> 'ALL' then @t_name else t_name end)

-- For all values
set @t_name = 'ALL'

select * from @t
where t_name = (case when @t_name <> 'ALL' then @t_name else t_name end)


------------------------------------
-- using Peso's solution
------------------------------------

-- For specific value
set @t_name = 'A'

select * from @t
where @t_name in ('all', t_name)

-- For all values
set @t_name = 'ALL'

select * from @t
where @t_name in ('all', t_name)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-28 : 10:23:23

Hi ALL,

Thanks for ur efforts. Its solved. I applied Harsha's solution.

Thanks again

,

Mahesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources