IsNull() on bigint's min value?

Question

Why does the following expression in SQL Server return -9223372036854775808 and not 123?

I am calling this from a stored proc where I can't 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

Being negative doesn't mean the value is NULL. NULL is the lack of any value at all.

Answer 2

@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 3

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

NULLIF(@t, -9223372036854775808)

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

Answer 4

Because @t is not null.

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

Answer 5

You seem to be assuming that -9223372036854775808 IS NULL which is incorrect. ISNULL(@t, 123) would only return NULL if @t IS NULL but it's not null since it has the value of -9223372036854775808 which is non-NULL.

Answer 6

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

Answer 7

To achieve what I think you want to achieve, try this:

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

or this:

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