Working with arrays in SQL Server

Question

I have an int ID array (a lot of flags that I can select) that I want to receive in a single database call, but a stored procedure.

Is there a way to work with an array of these IDs in SQL Server? I believe that this should be something with partitioning the array and then its loop (in sql). I just do not know how?

SQL Server 2008

Answer 1

EDIT: Example below

As noted in @Oded, the best option is a table with parameters.

However, if for some reason you cannot use these (possibly the limitations of your shell), you can use the following to perform table partitioning:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SplitToTable]
(   
    @List varchar(max), @Delim varchar(1)
)
RETURNS TABLE 
AS
RETURN
(
    WITH csvtbl(Start, [Stop]) AS (
        SELECT      Start = convert(bigint, 1), [Stop] = 
                    charindex(@Delim COLLATE Slovenian_BIN2, @list + @Delim)
        UNION ALL
        SELECT      Start = [Stop] + 1, [Stop] = charindex(@Delim 
                    COLLATE Slovenian_BIN2, @list + @Delim, [Stop] + 1)
        FROM        csvtbl
        WHERE       ([Stop] > 0)
    )
    SELECT      substring(@list, Start, CASE WHEN [Stop] > 0 THEN [Stop] - 
                Start ELSE 0 END) AS Value
    FROM        csvtbl
    WHERE       ([Stop] > 0)
)

100. , , :

OPTION (MAXRECURSION 1000)  -- or 0 for unlimited

SELECT *
FROM   MyTable as t
WHERE  t.ID IN (
       SELECT *
       FROM   dbo.SplitToTable('1,2,12,34,101', ',')
       )

..

Answer 2

:

• varchar , , ( , , , )
• XML XML (SQL Server 2005+ , ).
• (SQL Server 2008 +)

SQL Server 2008, .

Answer 3

, - ...

Declare @query as varchar(500)
Declare @valuesList as varchar(100)
set @valuesList = '1,2,3'
set @query = 'select * From tableName where id in ( ' + @valuesList + ')'
exec(@query)

Answer 4

DECLARE @t TABLE
(
    ID int
)

INSERT INTO @t
VALUES (1), (3), (5), (7), (9)

SELECT  STUFF(
        (
            SELECT  ',' + CAST(t.ID AS VARCHAR(10))
            FROM    @t t
            FOR     XML PATH('')
        ), 1, 1, '') AS CSV

SQLAuthority.