Selective Where statement

In my "where" section that's attached I need to use ONLY @searchName if len(@searchName) > 0

Otherwise use the @firstName or @lastName and NOT @searchName at all
Where	l.last_name Like '%' + IsNull(@lastName, l.last_name) + '%'
				or
			l.first_name Like '%' + IsNull(@firstName, l.first_name) + '%'
				or
			p.name like '%' + isnull(@searchName, p.name) + '%'

Open in new window

lrbristerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
tim_csConnect With a Mentor Commented:
WHERE
      ((l.last_name Like '%' + IsNull(@lastName, l.last_name) + '%' OR l.first_name Like '%' + IsNull(@firstName, l.first_name) + '%') AND LEN(@searchName) = 0)
      OR (p.name like '%' + isnull(@searchName, p.name) + '%' AND LEN(@searchName) > 0)

0
 
JestersGrindCommented:
You can use a case statement to create a conditional where clause.  Try this.

Greg


Where	l.last_name Like '%' + IsNull(@lastName, l.last_name) + '%'
				or
			l.first_name Like '%' + IsNull(@firstName, l.first_name) + '%'
				or
			--p.name like '%' + isnull(@searchName, p.name) + '%'
			p.name LIKE CASE WHEN LEN(@searchName) = 0 THEN p.name ELSE '%' + @searchName + '%' END

Open in new window

0
 
lluddenCommented:
Where (len(@searchName) > 0 AND p.name like '%' + isnull(@searchName, p.name) + '%')
OR
            (len(@searchName) = 0 AND l.last_name Like '%' + IsNull(@lastName, l.last_name) + '%')
                        or
            (len(@searchName) = 0 AND l.first_name Like '%' + IsNull(@firstName, l.first_name) + '%')

                  
0
 
lrbristerAuthor Commented:
This is final solution with actual valid data returned.
Thanks

WHERE
      ((l.last_name Like '%' + IsNull(@lastName, l.last_name) + '%' and l.first_name Like '%' + IsNull(@firstName, l.first_name) + '%') AND LEN(@searchName) = 0)
      OR (p.name like '%' + isnull(@searchName, p.name) + '%' AND LEN(@searchName) > 0)
0
 
sachinpatil10dCommented:
If you want to conditional statement in where clause, Dynamic Sql is the only option available.
0
All Courses

From novice to tech pro — start learning today.