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

• Glenn Dorling

  SSCertifiable

  Points: 7938

  More actions

  March 1, 2009 at 3:45 pm

  #951619

  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: 1004565

  More actions

  March 1, 2009 at 6:18 pm

  #951633

  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

  March 1, 2009 at 7:40 pm

  #951642

  >> 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.