Search code examples
sql-serversql-server-2017

SQL Server 2017 Table-valued Functions - filtering with multiple values instead of just one


I am using SQL Server 2017, version 14.0.2002.14 (Developer Edition (64-bit))

I am trying to create a function where users can pass in multiple values to filter a query.

I am normally able to do it with functions that ask for only a single parameter, using the template below:

CREATE FUNCTION func_1
(
@StartDate date,
@EndDate date
)
RETURNS TABLE AS 
RETURN
(
SELECT * FROM table_name 
WHERE  table_name.Date BETWEEN @StartDate and @EndDate
)

The user would be able to return filtered results by doing:

SELECT * FROM func_1('20190101', '20191231')


When trying to return results by passing in multiple values, this query works when executed on SQL Server:

-- Declare a TABLE variable to be used as a filter
DECLARE @Product TABLE
(
   Products varchar(10)
)
INSERT INTO @Product VALUES('value_1', 'value_2')

-- Return filtered results from query
SELECT Date, Product, Quantity
FROM   Table_Name
WHERE  Table_Name.Product IS IN (SELECT Products FROM @Product)

However, I'd like to create it as a function, which SQL Server does not like ...

CREATE FUNCTION func_2
(
    @StartDate date,
    @EndDate date,
    @Product TABLE
    (
    Product varchar(10)
    )
    INSERT INTO @Product VALUES()
)
RETURNS TABLE AS
RETURN
(
SELECT Date, Product, Quantity
FROM   Table_Name
WHERE  Date BETWEEN @StartDate AND @EndDate
AND    Table_Name.Product IS IN (SELECT Products FROM @Product)
)

The user should call this query and have it return the filtered results:

SELECT * FROM func_2('20190101', '20191231', ('value_1', 'value_2', 'value_3'))

Edit: Added the date arguments to func_2 as well i.e. the intended use of the function is to filter based on more than 1 parameter


Solution

  • I've done something similar. You have to create a table type that will be a list of string. For example :

    CREATE TYPE FilterType AS TABLE (MyFilter VARCHAR(10))
    

    Then, you can specify this parameter in a stored proc (not available for functions bug I think it's going to do what you want with a stored proc anyway).

    CREATE PROCEDURE MyProc (@filters FilterType  READONLY, ...)
    

    Once inside your procedure you can use it as a table

    SELECT * FROM YourTable t join @filters f on f.MyFilter = t.Column
    

    What's nice about that is that you can define the TYPE as you want and add as many parameters to it as you need.