SQL SERVER - Problems with COALESCE() function?

COALESCE : Return Types Returns the data type of expression with the highest data type precedence If all expressions are nonnullable, the result is typed as nonnullable (Emphasis added). Int had a higher precedence than varchar, so the return type of your COALESCE must be of type int. And obviously, your varchar value cannot be so converted You can re-write your where clause simply as: WHERE (@filterUID IS NULL AND @filterSID IS NULL) OR (@filterUID IS NOT NULL AND UID = @filterUID) OR (@filterSID IS NOT NULL AND SID = @filterSID).

COALESCE: Return Types Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable. (Emphasis added).

Int had a higher precedence than varchar, so the return type of your COALESCE must be of type int. And obviously, your varchar value cannot be so converted. You can re-write your where clause simply as: WHERE (@filterUID IS NULL AND @filterSID IS NULL) OR (@filterUID IS NOT NULL AND UID = @filterUID) OR (@filterSID IS NOT NULL AND SID = @filterSID).

1 Agree with that and hope it won't turn out that two parameters are just an example and actually there are twenty of them (whence the idea of using COALESCE may have originated)... – Andriy M Jun 15 at 19:26 Thank you for your fast response... and as Andriy said... I have a lot of params there and thought will be good to use COALESCE to achieve this. Shall I avoid using it? – Cristi Jun 16 at 10:03 1 @Cristi - if you want to use COALESCE, I'd use one COALESCE expression per data type (e.g. Put all of your varchar parameters in one COALESCE, all of your ints in another), and it won't try to perform any type conversions.

– Damien_The_Unbeliever Jun 16 at 10:24 Yep, I get your point... thanks Damien. Btw I will accept your answer since is exactly what I was looking for, but I want to wait a bit more to see if others would recommend something else as solution of my problem. Cheers!

– Cristi Jun 16 at 10:43 Damien, I have a quick question... I noticed that my logic is wrong and I need an "AND" instead "OR" there. So, if a filter param is set (not NULL), then use it in where clause, otherwise not. How to do it best if I have multiple params.

Could you show me a proposal of how to achieve such a select? Thanks! – Cristi Jun 16 at 12:41.

Try converting the parameters in Coalesce to Varchar For instance: (COALESCE(CONVERT(VARCHAR,@filterUID), CONVERT(VARCHAR,@filterSID)) IS NULL) OR.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.