CODEWITHSUNDEEP
sql-serversql-server-2017
yongsheng
yongsheng

Reputation: 396

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

Upvotes: 1

Views: 921

Answers (2)

Hedego
Hedego

Reputation: 294

The content from the following link does the same thing as what you want to achieve but only for one parameter.

sql-server-user-defined-functions

CREATE FUNCTION udfProductInYear (
@model_year INT
   )
RETURNS TABLE
AS
RETURN
   SELECT 
      product_name,
      model_year,
      list_price
  FROM
     production.products
  WHERE
    model_year = @model_year;

When accessing your function, use the following:

SELECT 
  product_name,
  list_price
FROM 
  udfProductInYear(2018);

Upvotes: 0

Danielle Paquette-Harvey
Danielle Paquette-Harvey

Reputation: 1791

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.

Upvotes: 2

Related Questions