Create a function that accepts list of ids?

Question

I would like to do create a SQL function like this (pseudocode):

function PeopleInCompanies(companyIds)
   SELECT * from Person WHERE CompanyId IN (companyIds)
end function

and call it like this:

define companyIds = 1,2,3
select * from PeopleInCompanies(companyIds)

is it even possible?

You can define a table type and pass the ids in using that type. — Gordon Linoff, Jan 22 '20 at 21:41
Does this help: https://stackoverflow.com/questions/42448596/how-do-i-pass-a-list-as-a-parameter-in-a-stored-procedure — VBoka, Jan 22 '20 at 21:44

Larnu · Answer 1 · 2020-01-22T21:52:27.917

You would need to use a table type parameter. Assuming that CompanyID is an int:

CREATE TYPE dbo.ints AS TABLE ([value] int);
GO

CREATE FUNCTION dbo.PeopleInCompanies (@CompanyID dbo.ints READONLY)
RETURNS TABLE
AS RETURN

    SELECT P.* --This should be replaced by the columns you need.
    FROM dbo.Person P
         JOIN @CompanyID CI ON P.CompanyID = CI.[Value];

Then you would call the function using:

DECLARE @CompanyID dbo.ints;
INSERT INTO @CompanyID
VALUES (1),(2),(3);

SELECT *
FROM dbo.PeopleInCompanies(@CompanyID);

score 0 · Answer 2 · answered Jan 22 '20 at 22:51

SQL Server does not support macro substitution. That said, you have the table type as Gordon and Larnu mentioned, or you can simply parse/split the delimited string

Just another option

Declare @companyIds varchar(max) = '1,2,3'

Select A.*
  From Person A
  Join string_split(@companyIds,',') B 
    on A.CompanyID = B.Value

Create a function that accepts list of ids?

2 Answers2