Help Using CASE instead of IF/. ELSE

Question

Help Using CASE instead of IF/. ELSE

Hogie503

Mr or Mrs. 500

Points: 543
More actions
December 17, 2018 at 12:12 pm

#373252

I have a user interface where the user is selecting a location code form a list of 25 location, we are passing the @Location to the SP
and returning only accounts base on their location.
What is the best way to write this query using CASE and not repeating with 25 IF / ELSE.,... Thanks
If (@Location = 01)
Select * from table_1
Where
ID = @ID
AND Printing = 0
AND Salary = 1
AND (Acct_Num LIKE     '___-___-01-___')
AND (Acct_Num NOT LIKE '000-___-__-___')
ELSE If( @Location = 02)
Select * from table_1
Where
ID = @ID
AND Printing = 0
AND Salary = 1
AND (Acct_Num LIKE     '___-___-02-___')
AND (Acct_Num NOT LIKE '000-___-__-___')
ELSE If( @Location = 03)
Select * from table_1
Where
ID = @ID
AND Printing = 0
AND Salary = 1
AND (Acct_Num LIKE     '___-___-03-___')
AND (Acct_Num NOT LIKE '000-___-__-___')

Viewing 5 posts - 1 through 4 (of 4 total)

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Assuming location is "01", "02", etc., when passed in, then:

Select * from table_1 Where ID = @ID AND Printing = 0 AND Salary = 1 AND (Acct_Num LIKE '___-___-' + @Location + '-___') AND (Acct_Num NOT LIKE '000-___-__-___')

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

It may be worth considering putting a computed LocationCode column on your table. This could be persisted and indexed and would make the above query much simpler.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

Phil Parkin - Monday, December 17, 2018 12:36 PM
It may be worth considering putting a computed LocationCode column on your table. This could be persisted and indexed and would make the above query much simpler.

True. Better yet, follow data normalization and don't store data as it's displayed, but atomically by column, for every column. Acct Num should be stored as 4 columns -- if that's indeed what it really is -- but it can still be displayed combined with dashes. A view can handle that task quite easily.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Hogie503 Mr or Mrs. 500 Points: 543 More actions · Answer 4

Thanks for the quick response, as a SQL beginner Scott first respond look fine to me, I don't quite understand the other suggestion
if possible a quick example might allow me to understand it better since you both agree on the second suggestion. I don't have access to the table
as I am just pulling the result for a report. Thanks again.