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

you can also use dynamic sql query – Ravi Oct 06 '15 at 10:53 — Ravi, Oct 06 '15 at 10:53

score 2 · Accepted Answer · answered Oct 06 '15 at 10:55

2

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.

answered Oct 06 '15 at 10:55

Giulio V.

180
8

Thanks a lot Giulio. I was actually just about to edit my post as I solved it with something similar. But instead I declared a new int where it got the records count and then checked it in a where clause. I think your way is cleaner though :) – Joakim Hansson Oct 06 '15 at 10:56
1

I think Switch condition is very heavy and if you have so many where condition or too much data then your sql query will be very slow – Ravi Oct 06 '15 at 11:03
@Ravi Thanks for the comment! I did actually never use switching in my final solution. I'll try to get som time over to post the final solution in my question! – Joakim Hansson Oct 07 '15 at 15:30

How to do a conditional JOIN with table valued parameter?

1 Answers1