April 6, 2012 at 5:40 pm
SQL Query returns the following values
ItemId IsProduced
101 1
102 0
103 0
From the GUI I am passing the value to Stored Procedure to filter the result.
If 0 (ALL) then i want all the rows.
If 1 (Produce) then all rows where IsProduced=1
If 2 (Non Produce) then all rows where IsProduced=0
How i can implement the query using case statements or any other method?
April 6, 2012 at 6:01 pm
This?
declare @tab table
(
ItemId int
, IsProduced int)
insert into @tab
select 101 ,1
union all select 102 ,0
union all select 103 ,0
declare @InputFromGUI tinyint
set @InputFromGUI = 2-- or 1 or 2
select *
from @tab t
where t.IsProduced = case when @InputFromGUI = 0 then t.IsProduced
when @InputFromGUI = 1 then 1
else 0
end
April 6, 2012 at 9:03 pm
If you can change what your input parameters mean you will have a much simpler time querying your data in my opinion. Let's say we changed it to mean this (changes underlined):
If NULL (ALL) then i want all the rows.
If 1 (Produce) then all rows where IsProduced=1
If 0 (Non Produce) then all rows where IsProduced=0
Then, slightly modifying ColdCoffee's code, the query can be simplified as:
declare @tab table
(
ItemId int
, IsProduced int)
insert into @tab
select 101 ,1
union all select 102 ,0
union all select 103 ,0
declare @InputFromGUI tinyint
set @InputFromGUI = 2 -- NULL or 0 or 1
select *
from @tab
where @InputFromGUI is null
or IsProduced = @InputFromGUI;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 10, 2012 at 9:25 am
jerry209 (4/6/2012)
SQL Query returns the following valuesItemId IsProduced
101 1
102 0
103 0
From the GUI I am passing the value to Stored Procedure to filter the result.
If 0 (ALL) then i want all the rows.
If 1 (Produce) then all rows where IsProduced=1
If 2 (Non Produce) then all rows where IsProduced=0
How i can implement the query using case statements or any other method?
Though untested, you might get better plan generation by simply using a series of IF Statements?
IF (ALL)
Query 1
IF (Produce)
Query 2
IF (Non Produce)
Query 3
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply