Working with arrays in SQL Server

Question

I have a int array of ID's (a lot of checkboxes I can choose from) which I want to get in one database call though a stored procedure.

Is there a way to work with an array of these ID's in SQL Server? I believe it should be something with splitting the array and then loop it (in sql). I just don't know how?

SQL Server 2008

Answer 1

There are many ways to do this:

• Pass in a varchar parameter of the values separated by commas and parse that out (not very efficient, but for a small amount of data, not too bad except for the parsing bit)
• Pass in XML and use the built in XML functions (SQL Server 2005+ has better support for this than earlier versions)
• Use table value parameters (SQL Server 2008+)

Since you are using SQL Server 2008, use table value parameters.

Answer 2

EDIT: Example below

As @Oded mentioned, table valued parameters is the best option.

However, if for some reason you can't use these (perhaps your calling framework's limitations), you can use the following to perform the split to table:

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

You need to be aware of the default recursion depth of 100. If this isn't enough, increase it by adding the following to your outer calling query:

OPTION (MAXRECURSION 1000)  -- or 0 for unlimited

EXAMPLE

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

It can be used on joins, etc., too.