How to do a conditional JOIN with table valued parameter?

Question

Okay so I have spent some time researching this but cannot seem to find a good solution.

I am currently creating a stored procedure that takes a set of optional parameters. The stored procedure will act as the "universal search query" for multiple tables and columns.

The stored procedure looks something like this (Keep in mind that this is just a stripped down version and the actual stored procedure has more columns etc.)

The '@ProductIdsParam IntList READONLY' is an example table valued parameter that I would like to JOIN if it is not empty. In other words, the query should only search by parameters that are not null/empty.

Calling the procedure and parsing the other parameters works just like it should. I might however have misunderstood and should not do a "universal search query" like this at all.

CREATE PROCEDURE [dbo].[usp_Search]

    @ProductIdParam INT = NULL,
    @CustomerNameParam NVARCHAR(100) = NULL,
    @PriceParam decimal = NULL,

    -- THIS IS WHAT I'D LIKE TO JOIN. BUT THE TABLE CAN BE EMPTY
    @ProductIdsParam IntList READONLY

AS
BEGIN
    SET NOCOUNT ON;

    SELECT DISTINCT 

    CustomerTransactionTable.first_name AS FirstName, 
    CustomerTransactionTable.last_name AS LastName,
    ProductTable.description AS ProductDescription,
    ProductTable.price as ProductPrice

FROM dbo.customer AS CustomerTransactionTable

-- JOINS
LEFT JOIN dbo.product AS ProductTable 
        ON CustomerTransactionTable.product_id = ProductTable.id


WHERE
    (ProductTable.id = @ProductIdParam OR @ProductIdParam IS NULL)
    AND (CustomerTransactionTable.first_name = @CustomerNameParam OR @CustomerNameParam IS NULL)
    AND (CustomerTransactionTable.price = @PriceParam OR @PriceParam IS NULL)

END

Answer 1

You can add the int table in LEFT join and then add a where condition based on the record count in the filter table. If @ProductIdsParam is declared as table, you should first count records in it and store the result in a varaible.

AND COALESCE(@ProductIdsParam.id, 0) = (CASE WHEN @ProductIdsCount = 0 THEN 0 ELSE ProductTable.id END)

In case @ProductIdsCount = 0 then you get always 0 = 0 so you get all the records, else you select only records where the productId in the filter table equals the ProductTable.id.

There are other (maybe cleaner) approaches possible though but I think this works.