CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Neeraj
Neeraj

Reputation: 4489

ISNULL giving wrong value in SQL server 2008

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.

Upvotes: 2

Views: 303

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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

Upvotes: 9

Vishal Gajjar
Vishal Gajjar

Reputation: 1019

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

Upvotes: 2

Related Questions