IsNull () by value of bigint min?

Question

Why does the following expression in SQL Server return -9223372036854775808 rather than 123 ?

I call this from a stored procedure where I cannot pass null parameters

 declare @t bigint; set @t = -9223372036854775808; --min value for bigint / long select ISNULL(@t, 123) 

Answer 1

Because:

 IF @t IS NOT NULL PRINT @t ELSE PRINT 123 

A negative value does not mean that the value is NULL. NULL is the absence of any value whatsoever.

Answer 2

Because @t is not null.

What made you think that the most negative value for bigint would be interpreted as null?

Answer 3

The ISNULL(@t, 123) function ISNULL(@t, 123) returns 123 if @t is NULL, otherwise it returns @t. You can do something like this.

 NULLIF(@t, -9223372036854775808) 

This will return NULL if @t is -9223372036854775808. NULLIF returns the first expression (@t) if the two expressions are not equal.

Answer 4

To achieve what I think you want to achieve, try the following:

 declare @t bigint; set @t = -9223372036854775808; --min value for bigint / long select ISNULL(NULLIF(@t, -9223372036854775808) , 123) 

or that:

 declare @t bigint; set @t = -9223372036854775808; --min value for bigint / long select case @t when -9223372036854775808 then 123 else @t end 

Answer 5

@t is not null because you assigned it a value. If you want ISNULL () to return 123, remove the assignment.

 declare @t bigint; select ISNULL(@t, 123) 

Answer 6

You seem to think that -9223372036854775808 IS NULL is incorrect. ISNULL(@t, 123) will only return NULL if @t IS NULL , but it is not null since it has a value of -9223372036854775808 , which is not NULL.

Answer 7

ISNULL returns the first non-zero value, they are both non-zero (have a value), so it returns the first.