Skip to content
Advertisement

What is the simplest way to put pass an array of values into a parameter of TVF

Objective:

I would like to have a parameter in my function to allow the user to input a list of values. Ideally, the simplest solution … Note: I dont have permissions to create tables in dbs.

Situation:

CREATE FUNCTION dbo.fnExample
(
    @StartDate AS Date -- Parameter 1
    @ParameterArray AS ... -- This would be the parameter that accepts a list of values
)
RETURNS TABLE
AS
RETURN
...code...
GO

Not sure how to pass several parameters in a TVF but this is how I would view the solution

SELECT *
FROM dbo.fnExample ('2019-01-01') and ([list of values])

Advertisement

Answer

Using a table type parameter, you can do something like the following:

CREATE TYPE dbo.SomeArray AS TABLE (SomeInt int); --Create the TYPE
GO
 --Now the Function
CREATE FUNCTION dbo.Example (@StartDate date, @Array dbo.SomeArray READONLY)
RETURNS TABLE
AS RETURN

    SELECT DATEADD(DAY, SomeInt, @StartDate) AS NewDate
    FROM @Array

GO

--Now to test
--Declare the TYPE
DECLARE @Array dbo.SomeArray;
--Insert the data
INSERT INTO @Array (SomeInt)
VALUES(7),(1654),(13);

--Test the function
SELECT *
FROM dbo.Example(GETDATE(), @Array) E;
GO

--Clean up
DROP FUNCTION dbo.Example;
DROP TYPE dbo.SomeArray;

How to JOIN:

FROM dbo.YourTable YT
     JOIN @Array A ON YT.SomeInt = A.SomeInt

How to use EXISTS:

WHERE EXISTS (SELECT 1 FROM @Array A WHERE YT.SomeInt = A.SomeInt)
7 People found this is helpful
Advertisement