How can I pass and use array in a stored procedure?

Question

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.

Answer 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) .

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)

• Subin Jacob: whats the usage of type_name?
• Nithesh Narayanan: its just a name.. user defined name of that table type
• Nithesh Narayanan: thing to take care is, you must pass the datatble from page as parameter having the same columns and names as of that table type

Answer 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.

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

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

• Subin Jacob: Passing XML as varchar to Stored Procedure, Then converting it to table value pair. right?
• Subin Jacob: But how can I use those table-value pairs in the above WHERE condition?
• Adriaan Stander: You will probably have to use it in conjunction with ROW_NUMBER msdn.microsoft.com/en-us/library/ms186734(v=sql.105).aspx and assign it to variables, or use sub selects.
• Subin Jacob: If I could do innerjoin this keyvalue pairs with the above select query, the result would be as we expected.right? But Can I Innerjoin?
• Adriaan Stander: You could do inner join, or IN sub select.

Answer 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);

• Chintan Khetiya: please add some description along with code so that's easy to understand.

Answer 4

Try this

Send comma separated String instead of array

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 )