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

. These two queries are as simple as possible. Each of them will use a different execution plan, different indexes — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 23, 2020 at 15:20
I asked the question because I do multiple queries in that table and everytime, I must handle those two cases... If I refactor, then I have twice the amount of code to handle (change in columns, where clause, etc). I'm surprised there is no native way to handle that case — RegularNormalDayGuy
– RegularNormalDayGuy, Commented Jul 23, 2020 at 15:30
The two queries are not the same at all. A query is just text that gets compiled to an execution plan, which takes into account existing indexes and data statistics to decide which indexes to use, which join, filter strategies, what to parallelise etc. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 23, 2020 at 15:33
The first query would only use a single index. The second would use two. If you tried to combine them the way granadaCoder proposes you could end up using only one index in the second case. Since the second query uses a very specific ID, you could end up scanning (and locking) 100 or 1000 rows when you could just touch a single row. The problem with catch-all queries is well known — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 23, 2020 at 15:34
One way to avoid this problem with catch-all queries is to add WITH RECOMPILE in your stored procedure. Instead of caching the execution plan, the server will parse the stored procedure each time it's called. This will negate any performance benefit you'd get from a stored procedure — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 23, 2020 at 15:37

granadaCoder · Accepted Answer · 2020-07-23 15:21:31Z

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

edited Jul 23, 2020 at 15:21

answered Jul 23, 2020 at 14:10

granadaCoder

28.2k11 gold badges124 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

RegularNormalDayGuy Over a year ago

I made a mistake in my query, I had fkID IS NULL in the first block, but it shouldn't be there, because if @fkID is NULL, I would like to display everything associated with the fkIDType.

RegularNormalDayGuy Over a year ago

I tried your query, but it removes values where fkID is NULL, since NULL = NULL is false

RegularNormalDayGuy Over a year ago

But then, if the @fkID is not null, it will return the null values, which I do not want, since a @fkID was specified

granadaCoder Over a year ago

I updated the answer. You basically need to cover all "cases" with the AND OR and CASE for your specific needs.

granadaCoder Over a year ago

Agreed it "will" hurt performance. (and upvoted your comment) But it depends on the number of rows and indexes on the table. Super (or I guess "more") flexible can put the hurt on high performance. #pickPoison More code to maintain ... vs "tricks" for the sql.

|

Collectives™ on Stack Overflow

Handle procedure that can take a null parameter

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related