SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Using IF statement in WHERE clause?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BusteR
Starting Member

Poland
17 Posts

Posted - 07/13/2005 :  19:07:57  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 07/13/2005 :  19:10:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Poland
17 Posts

Posted - 07/13/2005 :  19:29:28  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 07/13/2005 :  19:37:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Poland
17 Posts

Posted - 07/13/2005 :  19:50:22  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 07/13/2005 :  19:54:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Poland
17 Posts

Posted - 07/13/2005 :  20:04:49  Show Profile  Reply with Quote
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 ;]

Edited by - BusteR on 07/13/2005 20:17:07
Go to Top of Page

BusteR
Starting Member

Poland
17 Posts

Posted - 07/14/2005 :  05:18:32  Show Profile  Reply with Quote
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

Czech Republic
518 Posts

Posted - 07/14/2005 :  07:10:01  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
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.

Edited by - mmarovic on 07/14/2005 07:10:53
Go to Top of Page

BusteR
Starting Member

Poland
17 Posts

Posted - 07/14/2005 :  09:45:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000