Help Using CASE instead of IF/. ELSE

Question

Help Using CASE instead of IF/. ELSE

Hogie503

SSC-Addicted

Points: 483
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: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Phil Parkin SSC Guru Points: 245813 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ScottPletcher SSC Guru Points: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Hogie503 SSC-Addicted Points: 483 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.