Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Searching multiple fields

Author  Topic 

MichaelP
Jedi Yak

2489 Posts
Posted - 2002-12-09 : 14:08:37
What I want to do is to pass in FirstName, LastName, and Email, or any combination of those three. I want to return the contacts that meet any of those conditions. I'm close, but it's returning too much data.

CREATE TABLE #Contact (
	[ContactID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NOT NULL ,
	[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[AutoPhone] [bit] NOT NULL ,
	[AutoFax] [bit] NOT NULL ,
	[AutoEmail] [bit] NOT NULL ,
	[Cre_Date] [datetime] NOT NULL ,
	[Cng_Date] [datetime] NOT NULL 
)
SET IDENTITY_INSERT #Contact ON
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('62','1234','Sue','Brown','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('63','1234','Bob','Brown','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('64','1234','Harry','Brown','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('65','1234','Tom','Brown','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('66','1234','Scott','Brown','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('72','1234','Tony','Brown','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('2','1234','Jared','Collins','','','michaelp@televox.com','0','0','1','10/4/2002 5:31:01 PM','10/4/2002 5:31:01 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('99','1234','Michael','Collins','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('100','1234','Joe','Collins','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('106','1234','Mary','Collins','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('107','1234','Toni','Collins','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('108','1234','Jane','Collins','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('109','1234','Bobbi','Collins','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('110','1234','Jenna','Collins','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('111','1234','Tony','Collins','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('73','1234','Michael','Harris','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('78','1234','Tom','Harris','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('79','1234','Scott','Harris','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('80','1234','Mary','Harris','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('81','1234','Toni','Harris','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('43','1234','Jane','Jones','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('44','1234','Bobbi','Jones','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('45','1234','Jenna','Jones','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('52','1234','Tom','Pearson','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('53','1234','Scott','Pearson','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('54','1234','Mary','Pearson','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('87','1234','Joe','Small','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('88','1234','Sue','Small','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('89','1234','Bob','Small','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('90','1234','Harry','Small','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('91','1234','Tom','Small','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('92','1234','Scott','Small','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('94','1234','Toni','Small','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('24','1234','Bob','Smith','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('25','1234','Harry','Smith','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('26','1234','Tom','Smith','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('27','1234','Scott','Smith','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('28','1234','Mary','Smith','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('29','1234','Toni','Smith','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('30','1234','Jane','Smith','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('31','1234','Bobbi','Smith','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('127','1234','Sue','Soprano','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('128','1234','Bob','Soprano','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('129','1234','Harry','Soprano','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('130','1234','Tom','Soprano','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('131','1234','Scott','Soprano','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('132','1234','Mary','Soprano','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('133','1234','Toni','Soprano','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('134','1234','Jane','Soprano','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('135','1234','Bobbi','Soprano','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('136','1234','Jenna','Soprano','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('137','1234','Tony','Soprano','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('112','1234','Michael','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('113','1234','Joe','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('114','1234','Sue','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('115','1234','Bob','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('116','1234','Harry','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('117','1234','Tom','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('118','1234','Scott','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('119','1234','Mary','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('120','1234','Toni','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('121','1234','Jane','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('122','1234','Bobbi','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('123','1234','Jenna','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
Insert Into #contact(ContactID,UserID,FirstName,LastName,Phone,Fax,Email,AutoPhone,AutoFax,AutoEmail,Cre_Date,Cng_Date) VALUES ('124','1234','Tony','Springer','','','michaelp@televox.com','0','0','1','11/11/2002 2:38:45 PM','11/11/2002 2:38:45 PM')
SET IDENTITY_INSERT #Contact OFF


DECLARE @LastName VARCHAR(50)
DECLARE @FirstName VARCHAR(50)
DECLARE @Email VARCHAR(100)
DECLARE @UserID INT

SET @LastName = NULL
SET @FirstName = NULL
SET @Email = NULL
SET @UserID = 1234

--SET @LastName = 'smith'
SET @FirstName = 'toni'
--SET @Email = 'michaelp@televox.com'


SELECT CASE
	WHEN len(FirstName) = 0 THEN LastName
	WHEN len(LastName) = 0 THEN FirstName
	ELSE LastName + ', ' + FirstName 
	END AS ContactName,
	c.Email

FROM #Contact c
WHERE c.UserID = @UserID
AND 
	(
	((c.LastName LIKE @LastName + '%' OR @LastName IS NULL) OR (SOUNDEX(c.LastName) =  SOUNDEX(@LastName) OR @LastName IS NULL))
	OR
	((c.FirstName LIKE @FirstName + '%' OR @FirstName IS NULL) OR (SOUNDEX(c.FirstName) =  SOUNDEX(@FirstName) OR @FirstName IS NULL))
	OR
	((c.Email LIKE @Email + '%' OR @Email IS NULL) OR (SOUNDEX(c.Email) =  SOUNDEX(@Email) OR @Email IS NULL))
	)

ORDER BY c.LastName


DROP TABLE #Contact


<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2002-12-09 : 14:27:17
Try this for your WHERE clause:

WHERE
ISNULL(@LastName,'N/A') = LastName OR
ISNULL(@FirstName,'N/A') = FirstName OR
ISNULL(@Email,'N/A') = Email


Adjust with SOUNDEX as needed. The above should handle NULL values in the firstname and/or Email address in the data fine as well.


- Jeff
Go to Top of Page

allyanne
Starting Member

18 Posts
Posted - 2002-12-09 : 14:37:32
I think you have some ORs where you need ANDs. Try this:

SELECT CASE
WHEN len(FirstName) = 0 THEN LastName
WHEN len(LastName) = 0 THEN FirstName
ELSE LastName + ', ' + FirstName
END AS ContactName,
c.Email

FROM #Contact c
WHERE c.UserID = @UserID
AND (
c.LastName LIKE ISNULL(@LastName, '') + '%'
OR SOUNDEX(c.LastName) = SOUNDEX(@LastName)
)
AND (
c.FirstName LIKE ISNULL(@FirstName, '') + '%'
OR SOUNDEX(c.FirstName) = SOUNDEX(@FirstName)
)
AND (
c.Email LIKE ISNULL(@Email, '') + '%'
OR SOUNDEX(c.Email) = SOUNDEX(@Email)
)

ORDER BY c.LastName
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site