Handle procedure that can take a null parameter

Question

Say I have the following hierarchical table:

+------+------+----------+
| pkID | fkID | fkIDType |
+------+------+----------+
|    1 | NULL |        1 |
|    2 | NULL |        1 |
|    3 | NULL |        1 |
|    4 | 1    |        1 |
|    5 | 1    |        1 |
|    6 | NULL |        2 |
|    7 | 6    |        2 |
|    8 | 7    |        2 |
+------+------+----------+

I have a procedure that updates the table. The procedure takes two argument, a fkIDType and a fkID. fkIDType cannot be null. fkID can be NULL. I use both parameters to filter my table and then the procedures updates the table. If fkID is NULL, I want to update all the rows that are part of that fkIDType. So I have the following

IF @fkID IS NULL
BEGIN
    UPDATE
        dbo.table
    SET
        -- We set some info
    WHERE   
            fkIDType = @fkIDType
END
ELSE
BEGIN
    UPDATE
        dbo.table
    SET
        -- We set some info
    WHERE   
            fkIDType = @fkIDType
        AND fkID = @fkID
END

Is there a way to simplify the query without hurting performance ? I tried filtering like ISNULL(fkID, -999) = ISNULL(@fkID, -999), but then I will only get the null values instead of everything

Answer 1

  UPDATE
        dbo.table
  SET /* blah blah */
  FROM
       dbo.table origTable
  WHERE   
      fkIDType = @fkIDType
      AND

(((
((  origTable.fkID IS NULL AND @fkID IS NULL))
OR
      fkID =
          CASE
             WHEN @fkID IS NULL then origTable.fkID /* the trick is.. when your parameter IS NULL, match on the same/existing column, which will always match */
             ELSE @fkID
          END
)))

These types of "tricks" can hurt performance on large tables. So do not use them blindly.

FULL Generic Example:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Toy]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN

DROP TABLE [dbo].[Toy]

END

GO



CREATE TABLE [dbo].[Toy] (

[ToyUUID] [uniqueidentifier] primary key not null default NEWSEQUENTIALID() ,

ToyName varchar(64) not null,
[MacroStatusKey] smallint NOT NULL ,
[CreateDateUtc] [datetimeoffset] NOT NULL DEFAULT CURRENT_TIMESTAMP,
[UpdateDateUtc] [datetimeoffset] NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT Toy_RootName_UNIQUE UNIQUE (ToyName)

)

GO




INSERT INTO dbo.Toy ( ToyUUID , ToyName , [MacroStatusKey] ) 
SELECT '88888888-8888-8888-8888-000000000011', 'TeddyBear' ,   1
UNION ALL SELECT '88888888-8888-8888-8888-000000000021', 'Doll' ,  1
UNION ALL SELECT '88888888-8888-8888-8888-000000000071', 'Ball' ,  1
UNION ALL SELECT '88888888-8888-8888-8888-000000000081', 'CardboardBox' ,  1


SELECT * from dbo.Toy


Declare @MyToyUUID uniqueidentifier

SELECT @MyToyUUID = '88888888-8888-8888-8888-000000000011'

UPDATE
    dbo.Toy
SET MacroStatusKey = 44
FROM
    dbo.Toy origTable
WHERE   
    ToyUUID =
        CASE
            WHEN @MyToyUUID IS NULL then origTable.ToyUUID 
            ELSE @MyToyUUID
        END





SELECT * from dbo.Toy



SELECT @MyToyUUID = NULL

UPDATE
    dbo.Toy
SET MacroStatusKey = 55
FROM
    dbo.Toy origTable
WHERE   
    ToyUUID =
        CASE
            WHEN @MyToyUUID IS NULL then origTable.ToyUUID 
            ELSE @MyToyUUID
        END


SELECT * from dbo.Toy





-----------------




UPDATE
    dbo.Toy
SET MacroStatusKey = NULL
FROM
    dbo.Toy origTable
WHERE
    ToyName = 'TeddyBear'


SELECT ToyUUID, ToyName, MacroStatusKey from dbo.Toy


Declare @MyMacroStatus int
SELECT @MyMacroStatus = NULL

UPDATE
    dbo.Toy
SET MacroStatusKey = 77
FROM
    dbo.Toy origTable
WHERE   
    origTable.MacroStatusKey IS NULL
    OR
    MacroStatusKey =
        CASE
            WHEN @MyMacroStatus IS NULL then origTable.MacroStatusKey 
            ELSE @MyMacroStatus
        END


    SELECT 'With IS NULL OR check', ToyUUID, ToyName, MacroStatusKey from dbo.Toy





UPDATE
    dbo.Toy
SET MacroStatusKey = NULL
FROM
    dbo.Toy origTable
    
UPDATE
    dbo.Toy
SET MacroStatusKey = 111
FROM
    dbo.Toy origTable
WHERE
    ToyName = 'TeddyBear'


SELECT ToyUUID, ToyName, MacroStatusKey from dbo.Toy

SELECT @MyMacroStatus = 111

UPDATE
    dbo.Toy
SET MacroStatusKey = 112
FROM
    dbo.Toy origTable
WHERE   
    (( origTable.MacroStatusKey IS NULL AND @MyMacroStatus IS NULL))
    OR
    MacroStatusKey =
        CASE
            WHEN @MyMacroStatus IS NULL then origTable.MacroStatusKey 
            ELSE @MyMacroStatus
        END


SELECT ToyUUID, ToyName, MacroStatusKey from dbo.Toy

Results:

(below, seed data)

ToyUUID ToyName MacroStatusKey
88888888-8888-8888-8888-000000000011    TeddyBear   1
88888888-8888-8888-8888-000000000021    Doll    1
88888888-8888-8888-8888-000000000071    Ball    1
88888888-8888-8888-8888-000000000081    CardboardBox    1

(below, update where @MyToyUUID = '88888888-8888-8888-8888-000000000011')

ToyUUID ToyName MacroStatusKey
88888888-8888-8888-8888-000000000011    TeddyBear   44
88888888-8888-8888-8888-000000000021    Doll    1
88888888-8888-8888-8888-000000000071    Ball    1
88888888-8888-8888-8888-000000000081    CardboardBox    1

(below, update where @MyToyUUID is null)

ToyUUID ToyName MacroStatusKey
88888888-8888-8888-8888-000000000011    TeddyBear   55
88888888-8888-8888-8888-000000000021    Doll    55
88888888-8888-8888-8888-000000000071    Ball    55
88888888-8888-8888-8888-000000000081    CardboardBox    55