ISNULL function does not work correctly in SQL Server 2008

Question

Suppose this is a script:

DECLARE @result TABLE(Id BIGINT); DELETE FROM [Products].[Product] OUTPUT DELETED.[Id] INTO @result WHERE [Products].[Product].[Id] = 1589; 

So in the sequel I try:

1

 SELECT CAST(ISNULL([Id], -1) AS BIGINT) AS N'RetValId' FROM @result; 

When [Id] null returns null (nothing), but this returns -1:

2

 DECLARE @mi BIGINT; SET @mi = (SELECT [Id] FROM @result) SELECT CAST(ISNULL(@mi, -1) AS BIGINT) AS N'RetValId' 

Why? where is the problem with the first script?

Update

So, is there a way to check if Deleted Id is null. -1 And if not the returned identifier without declaring another variable? What is the easiest way?

Answer 1

If you do not have an entry for ID 1589, then there will be no entry in the DELETED table, if you have one, then it should return 1589.

So, if you haven't, I think it just returns nothing, because this statement has no input line:

 SELECT CAST(ISNULL([Id], -1) AS BIGINT) AS N'RetValId' FROM @result; 

(If you selected * from @result, there should be no lines)

The second returns -1, because first you set a variable that gets NULL after selection.

 DECLARE @mi BIGINT; SET @mi = (SELECT [Id] FROM @result) 

(If you select only @mi after this, then it should be NULL)

I think this explanation

UPDATED:

Can you try a little trick to get it without another available:

 SELECT CAST(ISNULL(MAX([ID]),-1) AS BIGINT) AS N'RetValId' FROM @result; 

Due to MAX, the insie statement will be NULL, so here's the trick. If something has been deleted, then there will be an ID. Hope this helps.

Answer 2

You can use a view that will return a single row with -1 and then do outer apply on @result .

 select isnull(R.Id, T.Id) RetValId from (values(-1)) as T(Id) outer apply @result as R 

Answer 3

An easy way to return null if no row in which it was deleted is the @@rowcount variable. It contains the number of rows affected by the previous operation:

 DELETE FROM [Products].[Product] WHERE [Products].[Product].[Id] = 1589; IF @@ROWCOUNT = 0 return null return 1589