-2

Can anyone tell me how to write a stored procedure that accepts an array of integers, and then uses that array in an IN clause?

Is this even possible? All the examples I've seen pass a comma-delimited string, which the stored procedure must parse.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466
  • 3
    Look into table type parameters. – Thom A Sep 27 '21 at 19:04
  • 1
    Start with Erland's discussion of [dynamic search conditions](https://www.sommarskog.se/dyn-search.html) – SMor Sep 27 '21 at 19:05
  • 1
    Table-valued parameters: https://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql – Aaron Bertrand Sep 27 '21 at 19:30
  • (My vote would be binding, but [possible duplicate](https://stackoverflow.com/q/42448596/61305)?) – Aaron Bertrand Sep 27 '21 at 19:36
  • Does this answer your question? [How do I pass a list as a parameter in a stored procedure?](https://stackoverflow.com/questions/42448596/how-do-i-pass-a-list-as-a-parameter-in-a-stored-procedure) – Dale K Sep 27 '21 at 19:39

1 Answers1

2

Create a user-defined table type:

CREATE TYPE [dbo].[IntArray] AS TABLE (
   Val [int] NOT NULL
)

Then use it as a stored procedure parameter:

CREATE Proc demo_type
@aryDemo IntArray
as
SELECT *
FROM MyTable
WHERE ID IN (
   SELECT val from @aryDemo
)
CMGeek
  • 136
  • 5