Array types in SQL Server functions

Question

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 have array types, no. It does have table TYPEs though. Creating a user-defined table type — Thom A
– Thom A ♦, Commented Feb 3, 2022 at 14:43
Does this answer your question? Can we have array type data in sql server 2008 — Igor
– Igor, Commented Feb 3, 2022 at 14:55
There's a really good series of articles on precisely this topic. Table-valued parameters are one popular solution, but there are others. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 3, 2022 at 14:55
What exactly are you trying to achieve? Perhaps you can just CROSS APPLY the function with a single ID? — Charlieface
– Charlieface, Commented Feb 3, 2022 at 17:33

Charlieface · Accepted Answer · 2022-02-03 17:35:02Z

1

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;

answered Feb 3, 2022 at 17:35

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Array types in SQL Server functions

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related