CODEWITHSUNDEEP
sqlsql-servert-sql
evgenyorlov1
evgenyorlov1

Reputation: 235

COALESCE doesn't return result

Database: SQL Server 2008

I have this SQL query:

DECLARE @var FLOAT;
SET @var = (SELECT field FROM table);
-- SELECT @var returns [NULL]

SELECT COALESCE(@var, 'NI');

[NULL] is an intermediate result for @var variable. I expect COALESCE to return 'NI', however I get following error message:

SQL Error[8114][S0005]: Error converting data type varchar to float.

What am I doing wrong?

Upvotes: 0

Views: 446

Answers (3)

HABO
HABO

Reputation: 15816

As explained at Coalesce: "Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable."

More generally, the rules of data type precedence state that when you combine a varchar ('NI') and a float (@var) in an expression (COALESCE(@var, 'NI')) then the varchar is converted implicitly to a float.

'NI' doesn't make a very good float (and isn't a witch).

Upvotes: 1

eremmel
eremmel

Reputation: 362

Is the value 'NI' just a string (char)? I expect so. COALESCE is expecting compatible types as arguments. In your case it is a float and a char/varchar. So you have first to cast the float to a char and then apply the COALESCE function:

print COALESCE(CAST(@var AS CHAR), 'NI');

Upvotes: -1

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

With

SELECT COALESCE(@var, 'NI');

the DBMS sees that you want to replace the float number @var with another value in case it is null. In order to do so, it tries to make the second parameter a float, too. But 'NI' cannot be converted to float of course.

Make the query return a string instead, i.e. convert your float to a string. E.g.:

SELECT COALESCE(CONVERT(VARCHAR(50), @var, 3), 'NI');

Upvotes: 2

Related Questions