Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Selective Where statement

Posted on 2011-09-30
5
225 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:lrbrister
5 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36891487
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
 
LVL 15

Accepted Solution

by:
tim_cs earned 500 total points
ID: 36891506
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
 
LVL 18

Expert Comment

by:lludden
ID: 36891507
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
 

Author Closing Comment

by:lrbrister
ID: 36891606
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
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36891654
If you want to conditional statement in where clause, Dynamic Sql is the only option available.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to restore or keep sql2000  backups useful... 2 12
SQLCMD Syntax 2 13
RESTORE MASTER DATABASE -- NOW 2 19
install report service in sccm2012 3 18
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question