Select records with CASE statement

Question

Post reply

Select records with CASE statement

henryvuong1000

SSC Eights!

Points: 821
More actions
March 8, 2016 at 1:09 pm

#313914

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

aaron.reese SSChampion Points: 13498 More actions · Answer 2

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

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 3

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

terry999 SSCertifiable Points: 5036 More actions · Answer 6

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