SQL SERVER - Problems with COALESCE() function

Question

I have this SQL function which is wrapped by a stored procedure:

ALTER FUNCTION dbo.GetObjList
(
    @filterUID int = NULL,
    @filterSID varchar(32) = NULL
)
RETURNS TABLE
AS

RETURN 

SELECT ROW_NUMBER() OVER (ORDER BY UID) AS [RowNumber], * 
FROM ObjTable
WHERE 
    (COALESCE(@filterUID, @filterSID) IS NULL) OR 
    (
        ((@filterUID IS NOT NULL) AND (UID = @filterUID)) OR
        ((@filterSID IS NOT NULL) AND (SID = @filterSID))
    )

Why would I receive such an error: "Conversion failed when converting the varchar value 'abc' to data type int." if I pass only @filterSID = 'abc' as parameters (and DEFAULT for others) ?

I noticed that COALESCE is responsible for the error.

EDIT: Now that I got the reason of the error... and given the fact that I have lots of params actually... what would you guys recommend as solution?

score 8 · Accepted Answer · edited Jun 20 '20 at 09:12

8

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)

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 15 '11 at 16:42

Damien_The_Unbeliever

234,701
27
340
448

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 '11 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? – Learner Jun 16 '11 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 '11 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! – Learner Jun 16 '11 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! – Learner Jun 16 '11 at 12:41

score 3 · Answer 2 · edited Jun 16 '11 at 12:20

3

Try converting the parameters in Coalesce to Varchar
For instance:

(COALESCE(CONVERT(VARCHAR,@filterUID), CONVERT(VARCHAR,@filterSID)) IS NULL) OR

edited Jun 16 '11 at 12:20

Andrei Sfat

8,440
5
49
69

answered Jun 15 '11 at 19:49

Raj Jayaswal

468
1
9
22

Thanks for that... I see your point, but i think is too 'long and ugly'... I guess I would opt for what Damien suggested, but is there somehow another solution? – Learner Jun 16 '11 at 10:12

SQL SERVER - Problems with COALESCE() function

2 Answers2

Linked