SQL SERVER - Problems with COALESCE() function

sql-server coalesce sql-function
Learner picture Learner · Jun 15, 2011 · Viewed 10.4k times · Source

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?

Answer

Damien_The_Unbeliever picture Damien_The_Unbeliever · Jun 15, 2011

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)

Related questions

Checking an input param if not Null and using it in where in SQL Server

What is the best way to include an input param in the WHERE clause but exclude it if it is null? There are a number of ways I believe, but I can't seem to remember then. Also could I use …

Read More
How to concatenate strings and commas in SQL Server?

I'm relatively new to MSSQL, so sorry if the question might sounds trivial. I want to concatenate multiple fields with a delimiter ,. However, when the field is empty, the extra , will be included in the result string as well. So …

Read More
COALESCE, IFNULL, or NZ() function that can be used in SQL Server and MS Access

I have a project that can use either SQL Server or MS Access as the data store. In one SELECT statement, I must perform a COALESCE operation on a single column and a single value, like this: SELECT COALESCE([Amount], 0) …

Read More