How to use one sql parameter to represent input array

Question

Is there a way to write sql for Oracle, MS SQL:

Select * from table where id in(:arr)


Select * from table where id in(@arr)

With one param in sql 'arr' to represent an array of items? I found examples that explode arr to @arr0,.., @arrn and feed array as n+1 separate parameters, not array, like this

Select * from table where id in(:arr0, :arr1, :arr2)


Select * from table where id in(@arr0, @arr1, @arr2)

Not what i want. These will cause change in sql query and this creates new execution plans based on number of parameter.

I ask for .net, c# and Oracle and MS SQL.

Thanks for constructive ideas!

/ip/

Answer 1

In addition to a Table Valued Parameter as Steve mentioned, there are a couple of other techniques available. For example you can parse a delimited string

Example

Declare @arr varchar(50) = '10,20,35'

Select A.*
 From  YourTable A
 Join string_split(@arr,',') B on A.ID=value

Or even

Select A.*
 From  YourTable A
 Where ID in ( select value from string_split(@arr,',') )