如何在存储过程中传递和使用数组？

In my stored procedure I need to select based on the user input

在我的存储过程中，我需要根据用户输入进行选择

The query would be like this

查询将是这样的

SELECT A,B FROM MYTABLE WHERE B=array[0] OR B=array[1] OR B=array[2]

The number of items in array is unknown to me. The user selection will decide the number of elements in the array.

我不知道数组中的项目数。用户选择将决定数组中的元素数量。

How can I achieve this? If I could do this, I could avoid using same procedure for each elements in the array.

我怎样才能做到这一点？如果我能做到这一点，我可以避免对数组中的每个元素使用相同的过程。

4 个解决方案

#1

Instead of array you can create an User Defined Table type

您可以创建用户定义的表类型而不是数组

CREATE TYPE dbo.type_name AS TABLE 
(
    column1 INT NOT NULL
)

Pass a single columned DataTable from page as parameter (values are same as in that array) .

从页面传递单个圆柱形DataTable作为参数（值与该数组中的值相同）。

And in procedure you can use it as follows

在程序中，您可以按如下方式使用它

CREATE PROCEDURE proc_name
    @array dbo.type_name READONLY
AS
   SELECT A,B FROM MYTABLE WHERE B IN (select column1 from @array)

#2

You could have a look at Use Table-Valued Parameters (Database Engine)

您可以查看使用表值参数（数据库引擎）

Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

表值参数是使用用户定义的表类型声明的。您可以使用表值参数将多行数据发送到Transact-SQL语句或例程（例如存储过程或函数），而无需创建临时表或许多参数。

Further to that, you might want to pass it as an XML parameter, and then convert that to a table in the SP. Using XML in SQL Server

除此之外，您可能希望将其作为XML参数传递，然后将其转换为SP中的表。在SQL Server中使用XML

You could even pass in a delimited string, and split that into a table Split strings the right way – or the next best way

你甚至可以传入一个分隔的字符串，并将其拆分成一个表格以正确的方式拆分字符串 - 或者是下一个最佳方式

#3

Please, get comma seprated string from array, is like that "12,23,25,256". Finally execute
as per below :
where @array_string is parameter of string of array:

SELECT A,B FROM MYTABLE WHERE B in(@array_string);

#4

Try this

尝试这个

Send comma separated String instead of array

发送逗号分隔的String而不是数组

DECLARE @array0 VARCHAR(MAX)
--Commaseprated string array0
DECLARE @tmpArray0 TABLE ( value1 VARCHAR(500) )
--table for Commaseprated string array0

WHILE CHARINDEX(',', @tmpArray0) > 0 
    BEGIN
        INSERT  INTO @tmpArray1
                SELECT  SUBSTRING(@array0, 1, ( CHARINDEX(',', @array0) - 1 ))
        SET @array0 = SUBSTRING(@array0, CHARINDEX(',', @array0) + 1,
                                LEN(@array0))
    END

DECLARE @array1 VARCHAR(MAX)
--Commaseprated string array1
DECLARE @tmpArray1 TABLE ( value1 VARCHAR(500) )
--table for Commaseprated string array1

WHILE CHARINDEX(',', @array1) > 0 
    BEGIN
        INSERT  INTO @tmpArray1
                SELECT  SUBSTRING(@array1, 1, ( CHARINDEX(',', @array1) - 1 ))
        SET @array1 = SUBSTRING(@array1, CHARINDEX(',', @array1) + 1,
                                LEN(@array1))
    END


DECLARE @array2 VARCHAR(MAX)
--Commaseprated string array2
DECLARE @tmpArray2 TABLE ( value1 VARCHAR(500) )
--table for Commaseprated string array2

WHILE CHARINDEX(',', @array2) > 0 
    BEGIN
        INSERT  INTO @tmpArray2
                SELECT  SUBSTRING(@array2, 1, ( CHARINDEX(',', @array2) - 1 ))
        SET @array2 = SUBSTRING(@array2, CHARINDEX(',', @array2) + 1,
                                LEN(@array2))
    END


SELECT  A ,
        B
FROM    MYTABLE
WHERE   B IN ( SELECT   value1
               FROM     @tmpArray0 AS ta )
        OR B IN ( SELECT    value1
                  FROM      @tmpArray1 AS ta )
        OR B IN ( SELECT    value1
                  FROM      @tmpArray2 AS ta )

#1