Is there a possibility to create a SQL Server function with arrays as parameters, to use it as an IN condition?
CREATE FUNCTION NAME
(@IDLIST ARRAY<varchar(32)>
)
...
RETURN
SELECT ID, NAME, DESCRIPTION
FROM TABLE_XYZ
WHERE ID IN @IDLIST
SQL Server doesn't support array types, but you can pass through a table variable using Table Types.
However, I don't think you need this here. You can just use a scalar parameter, and CROSS APPLY the function for each row of the outer table.
CREATE OR ALTER FUNCTION dbo.NAME
(@ID varchar(32))
RETURNS TABLE
AS RETURN
SELECT ID, NAME, DESCRIPTION
FROM TABLE_XYZ
WHERE ID = @ID;
GO
SELECT t.*, n.*
FROM OtherTable t
CROSS APPLY dbo.NAME (t.ID) n;
You can also do this against a table variable or a temp table.
Alternatively, if you have a hard-codeed list you can use a VALUES clause
SELECT t.*, n.*
FROM (VALUES
('a'),
('b')
) t(ID)
CROSS APPLY dbo.NAME (t.ID) n;
tableTYPEs though. Creating a user-defined table typeCROSS APPLYthe function with a single ID?