March 8, 2016 at 1:09 pm
I have a table like this
Table1
Items Qty
Item A 1
Item B 5
Item C 3
Item D 7
Item E 1
Item F 2
Item G 10
Item H 1
Item I 8
I need to write a statement like:
Declare @selection int = (can be 0, 1, or 2)
Select Items, Qty From Table1
Where
CASE
-- when @selection = 0, select all items
-- when @selection = 1, select items that have qty = 1
-- when @selection = 2, select items that have qty > 1
END
How do I implement my CASE statement? Thanks
March 8, 2016 at 1:50 pm
Dynamic SQL is the preferred solution for this kind of problems:
DECLARE @selection tinyint = 1 -- or 0 or 2
DECLARE @sql nvarchar(max) = N'
SELECT Items, Qty
FROM Table1
'
IF @selection = 1
SET @sql += ' WHERE qty = 1 '
IF @selection = 2
SET @sql += ' WHERE qty > 1 '
EXEC(@sql)
-- Gianluca Sartori
March 9, 2016 at 7:13 am
If you can I would change the code so that the input parameter is VARCHAR and use discreet values 'All','One','Multi'
There is no context behind the 0,1,2 so how would a user know what was valid and what each value represented, at least with the text you get some sort of descriptive reference.
0 and 1 are acceptable where there is a binary option and the parameter is suitably named (@exportAll: 1=yes, 0 = no) - make sure your parameter name asks a positive question to avoid ambiguity: @DoNotExport is not a good name because it is not clear what a NO answer will give you. You would be better to call it @DoExport and reverse the logic or if you can't reverse the logic then call it @SuppressExport. Suppress is a positive action
you may also want to adopt a defensive programming stance and validate the entry is one of the allowed values before executing the code. This is especially important if the code is being used to return data to a downstream application tier. Proper validation and raising of error messages, use of TRY...CATCH blocks and transaction scoping should also be considered.
Sorry, went a bit OT there
March 9, 2016 at 8:51 am
Please try the following. I haven't tried it but I've used a similar approach in other queries.
Select Items, Qty From Table1
Where
QTY >
-- This can be simplified but I left all options in for clarity
CASE
when @selection = 0 THEN -1 -- select all items
when @selection = 1 THEN 0 -- select items that have qty = 1
when @selection = 2 THEN (@selection - 1) -- select items that have qty >= @selection
END
AND QTY <
when @selection = 0 THEN 1000000 -- max that will ever exist in table
when @selection = 1 THEN 2 -- select items that have qty = >1 <2 QTY = 1
when @selection = 2 THEN 1000000 -- max that will ever exist
END
March 9, 2016 at 9:02 am
While this approach certainly works, it doesn't help the optimizer come up with a decent plan.
I would go with dynamic SQL instead.
-- Gianluca Sartori
March 9, 2016 at 9:38 am
souLTower (3/9/2016)
Please try the following. I haven't tried it but I've used a similar approach in other queries.
Select Items, Qty From Table1
Where
QTY >
-- This can be simplified but I left all options in for clarity
CASE
when @selection = 0 THEN -1 -- select all items
when @selection = 1 THEN 0 -- select items that have qty = 1
when @selection = 2 THEN (@selection - 1) -- select items that have qty >= @selection
END
AND QTY <
when @selection = 0 THEN 1000000 -- max that will ever exist in table
when @selection = 1 THEN 2 -- select items that have qty = >1 <2 QTY = 1
when @selection = 2 THEN 1000000 -- max that will ever exist
END
I'd also go with Gianluca's suggestion of dynamic SQL. You'd get an adhoc plan but it would be the best for the job.
Failing that, I'd go for something like this:
SELECT Items, Qty
FROM Table1
WHERE @selection = 0
OR (@selection = 1 AND qty = 1)
OR (@selection = 2 AND qty > 1)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 11, 2016 at 6:07 am
select 'Item A' item, 1 qty
into #t
union all
select 'Item B', 5
union all
select 'Item C', 3
union all
select 'Item D', 7
union all
select 'Item E', 1
union all
select 'Item F', 2
union all
select 'Item G', 10
union all
select 'Item H', 1
union all
select 'Item I', 8
Maybe this could be useful
Declare @selection int
--= (can be 0, 1, or 2)
set @selection =2
SELECT item, qty FROM
(
SELECT *, CASE @selection
WHEN 0 THEN 1
WHEN 1 THEN CASE WHEN qty =1 THEN 1 ELSE 0 END
WHEN 2 THEN CASE WHEN qty >1 THEN 1 ELSE 0 END
END as isValid
from #t
) as tResults
where isvalid =1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply