CODEWITHSUNDEEP
sqlt-sqlnull
Dries Geenen
Dries Geenen

Reputation: 600

SQL - Handling IS NULL in a script

I've made a SQL script with a bunch of if/else blocks doing stuff based on the data received. After testing I've noticed that I had forgotten about "= NULL" not working in SQL. Is there a decent way of handling possible null values in a SQL script?

My script used to contain things like:

SET @PreviousFolderId = (
    SELECT TOP(1) ObjectId
    FROM Document.DocumentFolder 
    WHERE Name = @FolderName
    AND ParentFolderId = @ParentFolderId
    ORDER BY CreationDate
)

Clearly this won't work if @ParentFolderId is NULL. The workaround I came up with is the following (please don't laugh):

DECLARE @PreviousFolderIdTable TABLE (PreviousFolderId INT)
DECLARE @Query NVARCHAR(MAX)
SET @Query = 
'SELECT TOP(1) ObjectId
    FROM Document.DocumentFolder 
    WHERE Name = ''' + @FolderName + '''
    AND ParentFolderId '
IF @ParentFolderId IS NULL
SET @Query += 'IS NULL'
ELSE SET @Query += CONCAT('= ',@ParentFolderId)
SET @Query += '
ORDER BY CreationDate'
DELETE FROM @PreviousFolderIdTable
INSERT INTO @PreviousFolderIdTable
EXECUTE(@Query)
SET @PreviousFolderId = (
    SELECT TOP(1) PreviousFolderId
    FROM @PreviousFolderIdTable
    ORDER BY PreviousFolderId
)

There has to be a better way to achieve the same effect. Not only is there way too much code to achieve this, it would take a while for someone to comprehend what I'm trying to do here, compared to the first query being easily readable.

Upvotes: 2

Views: 59

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

If you want to allow an empty @ParentFolderId you can use OR within your WHERE:

WHERE Name = @FolderName
  AND (@ParentFolderId IS NULL OR ParentFolderId = @ParentFolderId)

If you want to deal with NULL as a normal value you can use this

WHERE Name = @FolderName
  AND (ParentFolder IS NULL AND @ParentFolderId IS NULL OR ParentFolderId = @ParentFolderId)

Upvotes: 2

Related Questions