how to do coalesce with in

Question

how to do coalesce with in

Stylez

SSCrazy

Points: 2195
More actions
February 12, 2008 at 12:37 am

#69785

hi all i am testing a query which is defined like this
declare @id varchar(30)
declare @lastname varchar(30)
declare @jobs varchar(30)
SET @id = '('1','2')'
SET @jobs = null
SET @lastname = NULL
SELECT idcol,
jobscol,
lastnamecol
FROM tbl_profile
WHERE idcol in (COALESCE('1,2',idcol))
AND
lastnamecol = COALESCE(@lastname,lastnamecol) AND
jobscol = COALESCE(@jobs,jobscol)
is the COALESCE function possible for where IN Clause statements
thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I'm not sure what you're trying to do with Coalesce. Coalesce just returns the first non-null expression from the arguments passed to it. So

Coalesce('1,2',idcol) will return '1,2' always, since it's a constant and therefore not null.

What are you trying to achieve?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Stylez SSCrazy Points: 2195 More actions · Answer 2

i would like to kindly ask if we can incorporate a Coalesce Function inside a where in clause ?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

In a where clause, yes. As part of an IN, no. What are you trying to acheive?

Coalesce is used as follows:

WHERE SomeColumn = Coalesce(@Param1, @Param2, @Param3,0)

If @Param1 is not null, then @Param1 will be returned by the coalesce and the filter will be equivalent to SomeColumn = @Param1

If @Param1 is null, and @param2 is not null, then @Param2 will be returned by the coalesce and the filter will be equivalent to SomeColumn = @Param2

If @Param1 is null, @Param2 is null and @param3 is not null, then @Param3 will be returned by the coalesce and the filter will be equivalent to SomeColumn = @Param3

If all three of the parameters are null, then 0 is returned by the coalesce and the filter will be equivalent to SomeColumn = 0

Does that help?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Stylez SSCrazy Points: 2195 More actions · Answer 4

I am constructing a dynamic sql statement that's why i am using a Coalesce function in sql. i was trying to achieve the following

set @id = '1','2'

set @name = null

select id, name from sometable

where in (coalesce(@id,id))

can i achieve a result set by using this.

thank you very much

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Not without using dynamic SQL.

The other option is that you can use one of the split functions that are in the script library here to split the string apart into a rowset. Something like this

WHERE idcol in (select id from dbo.Split(@Ids)) OR @IDs is null.

Will probably mess badly with your index usage (as most of these search-type queries do), but should work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Stylez SSCrazy Points: 2195 More actions · Answer 6

thank you sir. can i ask you one last favor?

can you tell me how to construct a dynamic where clause statement

(@columnname = @columnvalue)

thank you very much

N.O.E.L.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

All you need to know about DYNAMIC SQL

http://www.sommarskog.se/dynamic_sql.html

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