Use of Coalesce function - SQL Server Forums

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)

Use of Coalesce function

Author

Topic

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-02-12 : 09:27:31

This is just a sample of using Coalesce function:

DECLARE @Cus_Name varchar(30),
@Cus_City varchar(30),
@Cus_Country varchar(30)

SET @Cus_Name = NULL
SET @Cus_City = 'Paris'
SET @Cus_Country = NULL

SELECT Cus_Name,
Cus_City,
Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
Cus_City = COALESCE(@Cus_City,Cus_City) AND
Cus_Country = COALESCE(@Cus_Country,Cus_Country)

What if I have comma delimited values in one of the parameters, for instance
set @Cus_City = 'Paris, London, New York'
How should i rewrite select statement?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 09:32:32

[code]DECLARE @Cus_Name VARCHAR(30),
@Cus_City VARCHAR(30),
@Cus_Country VARCHAR(30)

SELECT @Cus_Name = NULL,
@Cus_City = 'Paris',
@Cus_Country = NULL

SELECT Cus_Name,
Cus_City,
Cus_Country
FROM Customers
WHERE (@Cus_Name IS NULL OR @Cus_Name LIKE '%' + Cus_Name +'%')
AND (@Cus_City IS NULL OR @Cus_City LIKE '%' + Cus_City + '%')
AND (@Cus_Country IS NULL OR @Cus_Country LIKE '%' + Cus_Country + '%')[/code]

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-12 : 09:47:02

"What if I have comma delimited values in one of the parameters, for instance
set @Cus_City = 'Paris, London, New York'"


...
(@Cus_City IS NULL OR ' ' + @Cus_City + ',' LIKE '% ' + Cus_City + ',%')
...

but its going to be inefficient if your [Customers] table is large; if that's the case there are better ways of handling this type of problem (but they are more complicated too).

Note that this relies on the exact formatting of @Cus_City being delimited with a comma plus a space.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 09:51:12

Doesn't that depend on the data stored in Cus_City column?

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-12 : 11:34:42

Sure - if the Cus_City column can contain values such as "Paris, London" then its gonna pass-positive, and fail for the @Cus_City='London' case.

All I was after was preventing a partial match - so

@Cus_City='Paris'

will fail to match

Cus_City = 'XXXParisXXXX'

which I think is desirable

Or am I missing your point?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 11:40:38

I thought the input variable could have CSV values, and the table column not.

Peter Larsson
Helsingborg, Sweden

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-02-12 : 13:13:26

Thanks to all of you. This works great. Now I have another question.
What if one of the parameters looks like
@Cust_ID = '2,4,7'
but Cust_Id in the table has datatype int.
In this case the following line returns "error converting the varchar value '%' to a column of data type int."

(@Cust_ID IS NULL OR @Cust_ID LIKE '%' + Cust_ID +'%')
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 13:16:47

(@Cust_ID IS NULL OR ',' + @Cust_ID + ',' LIKE '%,' + CAST(Cust_ID AS VARCHAR) + ',%')

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 13:19:51

The suggestion above is to avoid "false" records.
When Cust_ID contains INT 1, and @Cust_ID is '11,34543,234234' you won't return the '1' record, but you will if you do not add the commas.

Peter Larsson
Helsingborg, Sweden

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-02-12 : 13:27:06

Thanks a lot, Peter.
It works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources