need help with case statement in Joins

• Chinna

  SSCertifiable

  Points: 5408

  More actions

  November 14, 2013 at 4:08 pm

  #395830

  declare @Parameter varchar(10)

  I need to have a conditional join based on the parameter value

  If @parameter = 'Yes'

  THEN JOIN CONDITION 1

  else

  then JOIN CONDITION 2

  tried below but didnt work

  (CASE when @parameter = 'Yes' THEN

  (LEFT JOIN dbo.test t ( NOLOCK ) ON a.id = t.id

  aND ISNULL(C.parameter,'') = @parameter )

  ELSE

  (LEFT JOIN dbo.test t ( NOLOCK ) ON a.id = t.id

  AND a.rowid = t.rowid

  AND ISNULL(C.parameter,'') = @parameter )

  END )

  Thanks,
  Chinna

  Its the Journey which gives you Happiness not the Destination-- Dan Millman

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 14, 2013 at 4:15 pm

  #1666579

  Here's an approach that would put you in the right direction syntactically.

  LEFT JOIN dbo.test t (NOLOCK)

  ON a.id = t.id

  AND (C.parameter = @parameter OR C.parameter IS NULL)

  AND a.rowid = CASE WHEN @parameter = 'Yes' THEN t.rowid ELSE a.rowid END

  But in terms of performance of this query I strongly recommend you'll have a look at Gails great article (http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/) to get a more detailed understanding regarding the possible performance issue you might suffer...

  Edit: I also removed the NOLOCK hint since this might result in duplicate or missing result sets. You'll need to optimize the query and/or the indexing related to it instead of just adding the NOLOCK hint...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Chinna

  SSCertifiable

  Points: 5408

  More actions

  November 14, 2013 at 4:26 pm

  #1666584

  Thanks for the response..

  I need to add few more fields in the case statement based on the parameter ..I will test it .

  Thanks

  Thanks,
  Chinna

  Its the Journey which gives you Happiness not the Destination-- Dan Millman

• Chinna

  SSCertifiable

  Points: 5408

  More actions

  November 14, 2013 at 5:13 pm

  #1666588

  Thanks lutz.. It worked like a charm.

  I will look into optimizing it now .

  Thanks,
  Chinna

  Its the Journey which gives you Happiness not the Destination-- Dan Millman