case vs if

Question

Post reply

case vs if

Sue-651097

Say Hey Kid

Points: 675
More actions
March 1, 2009 at 7:03 am

#206469

hi,
i have a stored procedure:
create proc pBaseValues
@kennung int
as
if @kennung = 1
select q.*
from tblAdressAnrede join qryBasiswerte q
on FKTextAnrede = FKTextZuordnung
if @kennung = 2 or @kennung = 4
select q.*
from tblAdressTitel join qryBasiswerte q
on FKTextTitel = FKTextZuordnung
if @kennung = 3
select q.*
from tblAdressBriefanrede join qryBasiswerte q
on FKTextBriefAnrede = FKTextZuordnung
if @kennung = 5
select q.*
from tblAdressArt join qryBasiswerte q
on FKTextArt = FKTextZuordnung
if @kennung = 6
select q.*
from tblAdressFamilienstand join qryBasiswerte q
on FKTextFamilienstand = FKTextZuordnung
-- and another 6 if s :)
my question is: is there a better way to realise this? i need just one proc and depending on the inputparameter, a different select statement (always the same columns) is returned.
can i do this with case?
Sue
Susanne

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

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

Glenn Dorling SSCertifiable Points: 7876 More actions · Answer 1

I'd do it completely differently.

First, create and populate a lookup table:

create table tablookup (

kennungint,

tabnamesysname,

joincolsysname)

insert into tablookup values (1, 'tblAdressAnrede', 'FKTextAnrede')

insert into tablookup values (2, 'tblAdressTitel', 'FKTextTitel')

.

Then your procedure's code could be:

.

declare @cmdnvarchar(4000)

select @cmd = N'select q.* from ' + tabname + N' join qryBasiswerte q on '

+ joincol + N' = FKTextZuordnung'

from tablookup

where kennung = @kennung

exec sp_executesql @cmd

.

It's not pretty, but it would get the job done.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Your IF statements are fine and they don't require the use of dynamic SQL.

My big question would be... why do you have separate tables for all that stuff?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nigelrivett SSCertifiable Points: 5472 More actions · Answer 3

>> always the same columns is returned

>> q.*

Both dangerous statements.

Does q have a simple PK?

If so I would return the those values then join to q to get the data.

Whether you do the final join in this SP or the calling SP is up to you.

I would stick with the if statments - you can put elses between them if you like.

Could also use a union all and put the if condition in a where clause.

There are a number of options but the idea of multiple if's isn't a bad one.

Is this doing text translation or something like that?

Cursors never.
DTS - only when needed and never to control.