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 2000 Forums
 Transact-SQL (2000)
 Using IF statement in WHERE clause?

Author  Topic 

BusteR
Starting Member

17 Posts

Posted - 2005-07-13 : 19:07:57
Hi

Is it possible to use IF statement in WHERE clause?
I mean... if it is possible then how? Or with what should i replace it?

What i want to do is create a stored procedure, that will have 5 parameters. I would like it to act like this [only theory, i know this procedure has a wrong syntax, but i want You to see what i would like to achieve]

create procedure dbo.raport @m int, @o int, @p int, @od smalldatetime, @do smalldatetime
as
select *
from report
where if (@m <> -1) begin m_id = @m end
AND if (@o <> -1) begin o_id = @o end
AND if (@p <> -1) begin p_id = @p end
AND dane_data >= @od
AND dane_data <= @do
ORDER BY d_date


I just want to replace 7 queries with only one.
Is it possible toachieve it or do i really have to write

if (@m <> -1) begin
if (@o <> -1) begin
if (@p <> -1) begin
.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 19:10:39
You can use CASE in a WHERE clause which acts just like an IF. But all you need to do is use a bunch of ORs it appears.

Tara
Go to Top of Page

BusteR
Starting Member

17 Posts

Posted - 2005-07-13 : 19:29:28
IMO CASE will not act like an IF.
I can't put m = @m as a result expression [or i don't know how ;] ]

With ORs... could You please give me an example? I can't imagine one...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 19:37:05
CASE would work, you'd just need more than one.

Here you go:



WHERE
(@m = -1 OR m_id = @m) AND
(@o = 1 OR o_id = @o) AND
(@p = -1 OR p_id = @p) AND
dane_date >= @od AND
date_date <= @do



The above should be equivalent to your pseudo-code. Just remember how ORs work, if the first part is true, then the whole thing evaluates to true, so it doesn't check the second part.

Tara
Go to Top of Page

BusteR
Starting Member

17 Posts

Posted - 2005-07-13 : 19:50:22
I guess i wasn't quite exact.
This procedure will create a list which will be shown in my reporting tool.
Parameters @m, @o and @p are representation of selected machine, operation and worker from list boxes. But when user won't select ie. machine, then my procedure should list operations performed by a worker on all machines. I set parameters to -1 when he is selecting any machine/operation/worker.
When he selects only machine, then my procedure should list all operations performed by all workers on that machine and so on.
When using Your WHERE clause, i would always select all machines, all operations and all workers when my parameters are set to -1 or m/o/p id is equal to selected by a user.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 19:54:13
We're going to need to see DDL for your table, INSERT INTO for sample data, the expected result set using the sample data and anything else like declaring of variables and initializing them in order to help you further as I'm thoroughly confused now.

It sounds like you could just use COALESCE though. Check out the SQLTeam.com article for details.

Tara
Go to Top of Page

BusteR
Starting Member

17 Posts

Posted - 2005-07-13 : 20:04:49
I guess it will be much easier if i will paste a code of a procedure that will work for me but is... stupid ;] I hate to code such things...


create procedure dbo.raport @m int, @o int, @p int, @od smalldatetime, @do smalldatetime
as

if (@m = -1 AND @o = -1 AND @p = -1)
select *
from widok_raport
where dane_data >= @od AND
dane_data <= @do
ORDER BY dane_data

if (@m <> -1 AND @o <> -1 AND @p <> -1)
select *
from widok_raport
where masz_id = @m AND
oper_id = @o AND
prac_id = @p AND
dane_data >= @od AND
dane_data <= @do
ORDER BY dane_data

if (@m <> -1 AND @o = -1 AND @p = -1)
select *
from widok_raport
where masz_id = @m AND
dane_data >= @od AND
dane_data <= @do
ORDER BY dane_data

if (@m = -1 AND @o <> -1 AND @p = -1)
select *
from widok_raport
where oper_id = @o AND
dane_data >= @od AND
dane_data <= @do
ORDER BY dane_data

if (@m = -1 AND @o = -1 AND @p <> -1)
select *
from widok_raport
where prac_id = @p AND
dane_data >= @od AND
dane_data <= @do
ORDER BY dane_data

if (@m <> -1 AND @o <> -1 AND @p = -1)
select *
from widok_raport
where masz_id = @m AND
oper_id = @o AND
dane_data >= @od AND
dane_data <= @do
ORDER BY dane_data

if (@m <> -1 AND @o = -1 AND @p <> -1)
select *
from widok_raport
where masz_id = @m AND
prac_id = @p AND
dane_data >= @od AND
dane_data <= @do
ORDER BY dane_data

if (@m = -1 AND @o <> -1 AND @p <> -1)
select *
from widok_raport
where oper_id = @o AND
prac_id = @p AND
dane_data >= @od AND
dane_data <= @do
ORDER BY dane_data


EDIT: i forgot the @m=-1 and @o=-1 and @p=-1 case ;]
Go to Top of Page

BusteR
Starting Member

17 Posts

Posted - 2005-07-14 : 05:18:32
is it possible to make it shorter? with just one select query??
In this example i can do it this way... but i guess i will have more such procedures later with more queries and more parameters and i really would like to know how to deal with them..

Best regards

P.s.
Thank You Tara for Your help ;]
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-14 : 07:10:01
Tara's script do the trick. From performance point of view your script may be the best, maybe you could package selects from there as procedures.
Go to Top of Page

BusteR
Starting Member

17 Posts

Posted - 2005-07-14 : 09:45:21
OMG

I'm so sorry ;] I think i was more asleep then awake when i wrote that post. Of course it will do the trick... lol ;]
Sorry again

Best regards
Go to Top of Page
   

- Advertisement -