SQL search for null values in a field

[wiscombe]

Hi I'm not sure how to search for a null AssetId within other stored AssetId's in a table.
I want to be able to allocate an ID to a new asset but also need to first search the table to see if there are any AssetId's that have been deleted and could be reused. eg There exists assets 1, 2, 3, 5, 6, 8. This would mean that there are assets 4, and 7 avaliable but how do I select them?

The current code is:
IF @AssetId = -1
BEGIN
SELECT @AssetId = MAX(AssetId) FROM Asset WHERE ProjectNumber = @ProjectNumber
IF @AssetId IS NULL SET @AssetId = 1
ELSE SET @AssetId = @AssetId + 1
END

And I'm not sure how to modify it so that I can set AssetId to the unused values. Any help would be appreciated.
Elise

 

[JarlH]

SELECT MIN(AssetId + 1) FROM Asset
WHERE AssetId NOT IN (SELECT AssetID - 1 FROM Asset);

 

[wiscombe]

Thanks for that.