ISNULL function in SQL Server 2008 not working properly

Question

Assume this script: ``` DECLARE @result TABLE(Id BIGINT); DELETE FROM [Products].[Product] OUTPUT DELETED.[Id] INTO @result WHERE [Products].[Product].[Id] = 1589; ``` So in continues I try : 1 ``` SELECT CAST(ISNULL([Id], -1) AS BIGINT) AS N'RetValId' FROM @result; ``` When `[Id]` is null returned null (nothing), but this one returned -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 first script? Update So is there any way to check if the Deleted Id is null returned -1 And if not Returned Id without declare another variable? what is the simplest way?

Accepted Answer

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

So if you don't have I think it simple returns nothing, because this statement has no input row:

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

(If you SELECT * from @result it should be no rows there)

The second one return the -1 because you set first to the variable which is getting the NULL value after the select.

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

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

I think that is the explanation

UPDATED:

May you can try a small trick to achive it without an other varriable:

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

Because of MAX the insie statement will be NULL, so here is the trick. If something was deleted, then the ID will be there. I hope it helped.

ISNULL function in SQL Server 2008 not working properly

Solution

Problem