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.

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.

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

'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

declare 
  @v1 as int = null
  ,@v2 as int = null
  ,@v3 as int = null
  
  
  select coalesce(@v1,@v2,@v3)
  

It will return NULL

Could you explain working: select COALESCE(NULL * 3, '') select COALESCE(NULL * 3, NULL) why it return 0 NULL Regards

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')

Thanks:) Now it's clear. Regards

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 = "" else txtEmpName.Text = ADODB.Recordser.Fields("empName") .Value End If

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 select 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!

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.

But getting to the original question:

The reason why

select coalesce(null,null,null) returns an error

but

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.

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)