asked on

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

JestersGrind

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

ASKER CERTIFIED SOLUTION

tim_cs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lludden

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) + '%')

Larry Brister

ASKER

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)

sachinpatil10d

If you want to conditional statement in where clause, Dynamic Sql is the only option available.