WildCard if not value selected

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 2005 Forums

Transact-SQL (2005)

WildCard if not value selected

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-05-07 : 09:04:38

HI Guys, I have a question.
I am converting Access SQL to SQL Server. One of the statements calls for a wildcard if the user does not select a value for the designated parm field. The value is selected from a cbolist (of names).

Current Statement:
And tblRetailer_Contact.faxcontact LIKE *

I substituted:
And tblRetailer_Contact.faxcontact LIKE ‘%@faxContacts%’

This might work if the User selects a name but if the User leaves it blank it will not work. Any ideas on how I go about establishing a wildcard if not name is selected?

DECLARE @FaxContact as varchar (50)

SET @H_Date = (SELECT StartDate FROM tblRpt_Params WHERE RptID = 5)
SET @Start_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @H_Date = (SELECT EndDate FROM tblRpt_Params WHERE RptID = 5)
SET @End_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @FaxContact = (SELECT FaxContact FROM tblRpt_Params WHERE RptID = 5)

SELECT tblEData.Timestamp As [TimeStamp],
LTRIM(RTrim([ResultsCustName])) AS CustName,
LTRIM(RTrim([ResultsPH])) AS Phone, Status As [Status],
FaxContact AS FaxContact,
ResultsPKey As ResultsKey
INTO tmpE_Callbacks
FROM tblEData
LEFT JOIN tblContact
ON tblEData.RetailerPrefix = tblContact.Prefix
WHERE tblEData.Timestamp BETWEEN @Start_Date And @End_Date
AND FaxContact Like '%@FaxContact%'

Thanx so much,
Trudye

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-07 : 09:08:00

[code]FaxContact Like '%' + @FaxContact + '%'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-07 : 09:57:01

This one should work for all values of @FaxContact including NULL:

...
AND FaxContact Like CASE WHEN @FaxContact IS NULL THEN FaxContact ELSE '%' + @FaxContact + '%' END

--
Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-07 : 10:06:48

A little bit simpler.

AND FaxContact LIKE '%' + COALESCE(@FaxContact, '') + '%' END

E 12°55'05.25"
N 56°04'39.16"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-07 : 10:16:40

Yep, nice. I have only done this on int-columns with equal-operations before and as far as I know the CASE-statement is the only thing doing the trick then...? Or is there some other way?

--
Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-07 : 10:28:43

AND FaxID = COALESCE(@FaxID, FaxID)

E 12°55'05.25"
N 56°04'39.16"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-08 : 02:39:24

DUH!

I gotta start thinking more...

--
Lumbago

Trudye
Posting Yak Master

169 Posts

Posted - 2008-05-08 : 13:26:36

Sorry it took soooo long to get back to you guys but I'm working on 5 projects at once and they are all HOT.

Here is what I ended up doing. I did the same thing harsh_athalye did but still had problems. Finally I wrote an IF statement that checks for NULL after the Declare statement:

IF (SELECT FaxContact FROM dbo.tblRpts_Param WHERE RptID = 5) is null
BEGIN
Set @FaxContact = ''
END
And then in the SELECT statement I wrote:
FaxContact Like '%' + @FaxContact + '%'

And for some reason it worked. The only thing I can think of was SQL did not consider the value in FaxContact = to NULL.
Go figure!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 13:58:11

quote:
Originally posted by Trudye

Sorry it took soooo long to get back to you guys but I'm working on 5 projects at once and they are all HOT.

Here is what I ended up doing. I did the same thing harsh_athalye did but still had problems. Finally I wrote an IF statement that checks for NULL after the Declare statement:

IF (SELECT FaxContact FROM dbo.tblRpts_Param WHERE RptID = 5) is null
BEGIN
Set @FaxContact = ''
END
And then in the SELECT statement I wrote:
FaxContact Like '%' + @FaxContact + '%'

And for some reason it worked. The only thing I can think of was SQL did not consider the value in FaxContact = to NULL.
Go figure!

Is nt this same soln Peso sugested on 05/07/2008 : 10:06:48

Trudye
Posting Yak Master

169 Posts

Posted - 2008-05-09 : 07:46:04

Oh for sure and thank you so much to everyone that responded. I was just providing feedback as to how I got it to work. I had to add the IF statement to what PESO suggested. So if someone else looks up this thread and PESO's statement doesn’t work for them they may want to consider adding the IF statement. Sorry for any confusion.

Thanx again everyone,
you guys have saved my life more times than I care to count,
Be well,
Trudye

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 07:51:34

You do not need the IF statement if you use the

AND COALESCE(FaxContact, '') LIKE '%' + COALESCE(@FaxContact, '') + '%'

suggestion. COALESCE evaluates @FaxContact to empty space ('') if @FaxContact is NULL, which is exactly the same thing as the IF statement does.

But I still think the COALESCE approach is simpler and cleaner.

EDIT: I can see now that FaxContact can be NULL

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-09 : 08:02:46

Also it will save an extra roundtrip to tblRpts_Param table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources