Dynamic JOIN condition on a Table

Question

I want to avoid string concatenation to create dynamic SQL query in SQL 2008.

How can I achieve the below functionality, if at all, efficiently ?

A table stores the various conditions a user had chosen to search DB by using one or more values to search tables from. For e.g. he can give SSN or DOB or both SSN and DOB.

So if he gives multiple values as input, then the query needs to find records matching ALL the criteria. This needs me to create queries from the below shown two tables

I always need to return personid, which may be from the person table, or the view.

Search conditions given by user will be stored in the 'conditions' table below

conditions
-------------
id,custid,dob,ssn,base

person
--------
id,firstname,ssn,dob

view
--------
id,personid,base

So, IF DOB is to be searched

SELECT p.personid from conditon c 
INNER JOIN person p
ON p.dob=c.dob
WHERE c.condition=1

IF SSN and base is to be searched

SELECT v.personid from conditon c 
INNER JOIN view v 
ON c.base=v.base
INNER JOIN person p
ON p.ssn=c.ssn
WHERE c.condition=1

IF SSN,DOB and base is to be searched

SELECT v.personid from conditon c 
INNER JOIN view v 
ON c.base=v.base
INNER JOIN person p
ON p.ssn=c.ssn
AND
p.dob=c.dob
WHERE c.condition=1

Your requirement to not use dynamic SQL is going to **really** impact the performance because the alternative is guaranteed to be [non-sargable](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable). You should really read [The curse and blessings of Dynamic SQL](http://www.sommarskog.se/dynamic_sql.html) — OMG Ponies, Sep 22 '10 at 22:14
Well we already have dynamic query at the moment. I was wondering if it can be eradicated for a smart and efficient query instead, which would also make our 2 page long SP more readable. — Saurabh Kumar, Sep 22 '10 at 22:44

Charles Bretana · Accepted Answer · 2010-09-22T22:54:05.843

Use the pattern below with IsNull() or Coalesce() function

  Where ColumnName = IsNull(@InParameter, ColumnName)

or tyhe same pattern in a Join Condition

   Join Table a 
      On [Other stuff]
           And ColumnName = IsNull(@InParameter, ColumnName)

And then, if the parameter is supplied, the ql will filter or join based on the parameter value, if it not supplied or is null, the query will ignore it (ColumnValue = ColumnVale is always true (except for nullable columns)

EDITED: to add another predicate to Join Condition. because, in the case where you did not specifiy a parameter value, the join condition would always be true, and, unless some other predicate condition is in the join, you would effectively get a cartesian product...

Dynamic JOIN condition on a Table

1 Answers1