ISNULL giving wrong value in SQL server 2008

Question

I have a scenario to check if first variable is null then shows to another variable value,for that i am using ISNULL but it gives wrong value.I don't know is wrong in my code given below :

DECLARE @str1 VARCHAR(5) = NULL,
        @str2 VARCHAR(10) = 'SQL Strings';

PRINT ISNULL(@str1, @str2);

But it print only SQL S.

Answer 1

That is because the type is determined by the first column, even if it is NULL. If you do:

DECLARE @str1 VARCHAR(10) = NULL,
        @str2 VARCHAR(10) = 'SQL Strings';

PRINT ISNULL(@str1, @str2);

Then you will get the expected answer.

The documentation says:

Replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

In other words, the second value is always converted to the first type. As a note, this is different from coalesce() where SQL Server tries harder to determine the type. So, coalesce(@str1, @str2) does what you expect (see here).

Answer 2

ISNULL() Returns same data type as check_expression, (1st arguement). Since it is only 5 characters the string is truncated:

You need to use COALESCE instead:

DECLARE @str1 VARCHAR(5) = NULL,
        @str2 VARCHAR(10) = 'SQL Strings';

PRINT COALESCE(@str1, @str2);
PRINT ISNULL(@str1, @str2);