Author |
Topic |
BusteR
Starting Member
17 Posts |
Posted - 2005-07-13 : 19:07:57
|
HiIs 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 smalldatetimeas 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 writeif (@m <> -1) beginif (@o <> -1) beginif (@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 |
|
|
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... |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
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 smalldatetimeasif (@m = -1 AND @o = -1 AND @p = -1)select *from widok_raportwhere dane_data >= @od AND dane_data <= @doORDER BY dane_dataif (@m <> -1 AND @o <> -1 AND @p <> -1)select *from widok_raportwhere masz_id = @m AND oper_id = @o AND prac_id = @p AND dane_data >= @od AND dane_data <= @doORDER BY dane_dataif (@m <> -1 AND @o = -1 AND @p = -1)select *from widok_raportwhere masz_id = @m AND dane_data >= @od AND dane_data <= @doORDER BY dane_dataif (@m = -1 AND @o <> -1 AND @p = -1)select *from widok_raportwhere oper_id = @o AND dane_data >= @od AND dane_data <= @doORDER BY dane_dataif (@m = -1 AND @o = -1 AND @p <> -1)select *from widok_raportwhere prac_id = @p AND dane_data >= @od AND dane_data <= @doORDER BY dane_dataif (@m <> -1 AND @o <> -1 AND @p = -1)select *from widok_raportwhere masz_id = @m AND oper_id = @o AND dane_data >= @od AND dane_data <= @doORDER BY dane_dataif (@m <> -1 AND @o = -1 AND @p <> -1)select *from widok_raportwhere masz_id = @m AND prac_id = @p AND dane_data >= @od AND dane_data <= @doORDER BY dane_dataif (@m = -1 AND @o <> -1 AND @p <> -1)select *from widok_raportwhere oper_id = @o AND prac_id = @p AND dane_data >= @od AND dane_data <= @doORDER BY dane_data EDIT: i forgot the @m=-1 and @o=-1 and @p=-1 case ;] |
|
|
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 regardsP.s.Thank You Tara for Your help ;] |
|
|
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. |
|
|
BusteR
Starting Member
17 Posts |
Posted - 2005-07-14 : 09:45:21
|
OMGI'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 againBest regards |
|
|
|