When I execute this query in 2008 R2 it get following msg: At least one of the arguments to COALESCE must be a typed NULL.
And in 2012 I get: At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
The message description means that at least one parameter should be typed NULL, means should have a datatype.
And when you add 1 to the NULL, the SQL Server engine automatically type casts the second param to INT.
Thus your first query fails and second one executes.
commented on Jul 11 2012 12:25AM
I guess it's connected with this note in Book Online: "At least one of the null values must be a typed NULL.".
In the first query(select coalesce(null,null,null) ) all arguments are not typed NULLs.
By the way, when I run first query it returns this error:
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be a typed NULL.
commented on Jul 11 2012 12:27AM
The COALESCE() is an expression; expressions have a data type. Your first example cannto be typred. Anohter problem is that the data type is the highest type in the list. Noobs will add "ELSE 'nothing' in an attempt to do display fomatting in the DB, but the CAST all the THEN clauses to strings
commented on Jul 11 2012 10:25AM
'NULL' value in T-SQL can be in two forms. That's typed and untyped. Typed null is when a variable gets a null value, yet having a data type. Untyped is when you directly use the null value (without using a variable). But when you say 'NULL+1' that expression will be evaluated and type casted into 'INT'. It seems since the COALSCE expects at leasts one 'typed' value in it's input parameter list, you will not get any error in your second expression. Check the following code sample
@v1 as int = null
,@v2 as int = null
,@v3 as int = null
It will return NULL
commented on Jul 15 2012 8:50PM
Could you explain working:
select COALESCE(NULL * 3, '')
select COALESCE(NULL * 3, NULL)
why it return
commented on Jul 27 2012 5:30AM
select COALESCE(NULL * 3, '') is giving 0 because, when you multiple NULL with 3 the result will be NULL, but it implicitly converts the NULL value to INT datatype.
And because first param is NULL, so second param value cast'ed by INT is returned, which comes to be 0.
Check this: select SQLVARIANTPROPERTY(COALESCE(NULL * 3, '') , 'BaseType')
commented on Jul 27 2012 6:05AM
Now it's clear.
commented on Jul 27 2012 6:28AM
I recalled my memory in the days of Visual Basic6 and trying to share my experience.
I always use a statement ADODB.Recordser.Fields("empName") .Value & ""
to handle NULL values efficiently.
I have seen people handle it like -
If isnull(ADODB.Recordser.Fields("empName") .Value) then
txtEmpName.Text = ""
txtEmpName.Text = ADODB.Recordser.Fields("empName") .Value
Coming to the point, it converts Null value to string. Same thing is happening in the SQL server too, but, depends upon what type of value you add with null for e.g.
If you run below script on SQL server and see the definition using sp_help CoalesceTest you will fine it is returning int and varchar data type.
create view CoalesceTest as
coalesce(null, null+1, null) as CoalesceValue1
,coalesce(null, null+'', null) as CoalesceValue2
,coalesce(null, null+'2012-06-30', null) as CoalesceValue3
Summary, null+1 will no longer remain actual "null it will null string!
commented on Aug 6 2012 4:19AM
RE: SELECT(COALESCE(null * 3, '') returning NULL
Manoj is correct. It depends upon the datatype.
Another way to see this is SELECT(COALESCE(null + 'x','y'))
This will return the value 'y'.
But SELECT(COALESCE(null + 1,'y'))
will return this error message:
Syntax error converting the varchar value 'y' to a column of data type int.
However SELECT(COALESCE(null + 'y', 1))
will correctly return the value 1.
commented on Aug 7 2012 8:51AM
But getting to the original question:
The reason why
select coalesce(null,null,null) returns an error
select coalesce(null,null + 1,null) returns null
is because "null + 1" is not a "null constant".
The expression "null + 1" has to get evaluated. It is run through some functions which evaluate to a null value.
commented on Aug 7 2012 9:07AM
Another question would be...
Is there ever a real world case for including the NULL keyword in a COALESCE call?
COALESCE(NULL, AddressLine1, @myVar1, NULL, AddressLine2, @myVar2, NULL)
Under what circumstance would any of the NULL's above be of any significance?
Would the result ever be any different than::
COALESCE(AddressLine1, @myVar1, AddressLine2, @myVar2)
commented on Aug 7 2012 10:33AM