There is very slightly difference between the ISNULL and COALESCE functions.
Both the function Replaces the NULL value with the other specified replacement value but there is a very minor difference between them.
e.g. for ISNULL
DECLARE @var varchar(3)
SET @var = NULL
select ISNULL(@var,'abc')
The out put of this query is 'abc'.
e.g for COALESCE
DECLARE @var varchar(3)
SET @var = NULL
select COALESCE (@var,'abc')
The out put of this query is also 'abc'.
Now i am discussing the difference between them..
The difference is:
"The result of ISNULL() function always takes on the datatype of the first parameter (regardless of whether it is NULL or NOT NULL)
while for COALESCE works, it works like a CASE expression."
The difference between them will be more clear by this example.
DECLARE @var varchar(3)
SET @var = NULL
select ISNULL(@var,'abcdef')
The out put of this query is 'abc'. --as we mention the datatype of @var as varchar of size "3".
----
DECLARE @var varchar(3)
SET @var = NULL
select COALESCE (@var,'abcdef')
The out put of this query is 'abcdef'. -- Regardless the size of the @var.
------
I think now the difference is clear..
|
No responses found. Be the first to respond and make money from revenue sharing program.
|