0

I have a stored procedure which receives multiple table-type parameters and I want to filter the data using those parameters. The problem with that is those parameters can contain no rows and in that case a join will not work properly.

CREATE TYPE [dbo].[IntListTableType] AS TABLE ([Id] INT NULL);

version 1 - using left joins and where conditions

CREATE PROCEDURE [dbo].[FilterUsers]    
    @UserRoles IntListTableType READONLY
       ,@UserTypes IntListTableType READONLY
AS 
    BEGIN
          SELECT UserId, Name 
          FROM Users u
          LEFT JOIN @UserRoles ur on u.RoleId = ur.Id
          LEFT JOIN @UserTypes ut on u.UserTypeId = ut.Id
          WHERE 
             (NOT EXISTS(SELECT TOP 1 1 FROM @UserRoles) OR ur.Id IS NOT NULL)
             AND 
             (NOT EXISTS(SELECT TOP 1 1 FROM @UserTypes) OR ut.Id IS NOT NULL)
       END

version 2 - without joins

CREATE PROCEDURE [dbo].[FilterUsers]    
    @UserRoles IntListTableType READONLY
       ,@UserTypes IntListTableType READONLY
AS 
    BEGIN
          SELECT UserId, Name 
          FROM Users u
          WHERE 
             (NOT EXISTS(SELECT TOP 1 1 FROM @UserRoles) OR EXISTS(SELECT TOP 1 1 FROM @UserRoles ur where u.RoleId = ur.Id))
             AND 
             (NOT EXISTS(SELECT TOP 1 1 FROM @UserTypes) OR EXISTS(SELECT TOP 1 1 FROM @UserTypes ut where u.UserTypeId = ut.Id))
       END

version 3 - dynamic SQL ???

I'm trying to find a solution from the performance perspective, and also to avoid parameter sniffing.

8
  • Version 2 is likely what you want, however, have you considered adding RECOMPILE to your OPTION clause in the query?
    – Thom A
    Commented Nov 9, 2022 at 11:18
  • Side note, you don't need a TOP (1) in an EXISTS clause; the data engine automatically "short circuits" as soon as it finds a row.
    – Thom A
    Commented Nov 9, 2022 at 11:18
  • Also indexing your column ID in your TYPE dbo.IntListTableType will be a good idea.
    – Thom A
    Commented Nov 9, 2022 at 11:25
  • the stored procedure has the OPTION RECOMPILE but that is creating a new execution plan each time affecting the performance Commented Nov 9, 2022 at 11:43
  • So you are saying that the cost of rebuilding the plan is more expensive that the instance using a plan that isn't relevant? For such a simple query, I very much doubt this.
    – Thom A
    Commented Nov 9, 2022 at 11:48

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.