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/

NO, AFAIK there is no way to use a single parameter to represent multiple values. You could explore the [Table Valued Parameters](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters) approach using a Stored Procedure instead — Steve, Feb 29 '20 at 14:14

score 2 · Answer 1 · answered Feb 29 '20 at 15:59

I believe Table Value Parameter is good option for this case. Have a look at a sample code below in SQL Server.

-- Your table
CREATE TABLE SampleTable
(
    ID          INT
)

INSERT INTO SampleTable VALUES
(1010),
(2010),       
(3010),
(4010),
(5010),      
(6010),      
(7010),      
(8030)
GO

-- Create a TABLE type in SQL database which you can fill from front-end code
CREATE TYPE ParameterTableType AS TABLE  
(
    ParameterID INT
    --, some other columns
)
GO

-- Create a stored proc using table type defined above
CREATE PROCEDURE ParameterArrayProcedure
(
    @ParameterTable AS ParameterTableType READONLY
)
AS
BEGIN

    SELECT
        S.*
    FROM SampleTable S
    INNER JOIN @ParameterTable P ON S.ID = P.ParameterID

END
GO

-- Populated table type variable
DECLARE @ParameterTable AS ParameterTableType
INSERT INTO @ParameterTable (ParameterID) VALUES (1010), (4010), (7010)

EXECUTE ParameterArrayProcedure @ParameterTable

DROP PROCEDURE ParameterArrayProcedure
DROP TYPE ParameterTableType
DROP TABLE SampleTable
GO

Apart from Table Value Parameter, you can also use Json or XML values as SQL parameter but yes, it will definitely change your execution plan accordingly.

@MT0, In C#, you need to create `DataTable` with the same columns defined in TVP and use it as SP parameter. SQL will itself map it with TVP if # of columns and their respective datatype matches. — iVad, Feb 29 '20 at 16:15

score 1 · Accepted Answer · answered Feb 29 '20 at 14:21

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,',') )

answered Feb 29 '20 at 14:21

John Cappelletti

79,615
7
44
66

Well, this is not using parameters and of course the execution plan is ruined as well. – Steve Feb 29 '20 at 14:36
@Steve Just an alternative ... Perhaps I have a different read than you. :) – John Cappelletti Feb 29 '20 at 14:38

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

Oracle

In other languages (i.e. Java) you can pass an SQL collection as a bind parameter and directly use it in an SQL statement.

However, C# does not support passing SQL collections and only supports passing OracleCollectionType.PLSQLAssociativeArray (documentation link) which is a PL/SQL only data-type and cannot be used (directly) in SQL statements.

To pass an array, you would need to pass a PLSQLAssociativeArray to a PLSQL stored procedure and use that to convert it to an SQL collection that you can use in an SQL statement. An example of a procedure to convert from a PL/SQL associative array to an SQL collection is:

CREATE TYPE IntList AS TABLE OF INTEGER
/

CREATE PACKAGE tools IS
  TYPE IntMap IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

  FUNCTION IntMapToList(
    i_map IntMap
  ) RETURN IntList;
END;
/

CREATE PACKAGE BODY tools IS
  FUNCTION IntMapToList(
    i_map IntMap
  ) RETURN IntList
  IS
    o_list IntList := IntList();
    i      BINARY_INTEGER;
  BEGIN
    IF i_map IS NOT NULL THEN
      i := o_list.FIRST;
      WHILE i IS NOT NULL LOOP
        o_list.EXTEND;
        o_list( o_list.COUNT ) := i_map( i );
        i := i_map.NEXT( i );
      END LOOP;
    END IF;
    RETURN o_list;
  END;
END;
/

How to use one sql parameter to represent input array

3 Answers3

Oracle