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

I pass the array to the stored procedures and return the data based on those ID's — janhartmann
– janhartmann, Commented Feb 21, 2011 at 14:05

Oded · Accepted Answer · 2011-02-21 14:04:14Z

5

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.

answered Feb 21, 2011 at 14:04

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

IamIC · Accepted Answer · 2011-02-22 15:12:38Z

1

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.

edited Feb 22, 2011 at 15:12

answered Feb 21, 2011 at 18:02

IamIC

18.4k20 gold badges96 silver badges162 bronze badges

2 Comments

janhartmann Over a year ago

Can you provide an example to use this function?

janhartmann Over a year ago

This works absolutely great. Is it possible you have a reversed function thats takes a table and returns it as string array?

Muhammad Akhtar · Accepted Answer · 2011-02-21 14:06:07Z

1

I think you need something like...

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)

answered Feb 21, 2011 at 14:06

Muhammad Akhtar

52.2k37 gold badges142 silver badges191 bronze badges

3 Comments

Muhammad Akhtar Over a year ago

@meep, it will be definitely fit. try it :)

janhartmann Over a year ago

Thats the bad thing about this. Even if I use database paramaters it will still allow SQL injection in this solution.

IamIC Over a year ago

This is a poor solution. Use table value parameters (SQL Server 2008+) as @Oded said.

IamIC · Accepted Answer · 2011-03-01 14:48:03Z

0

TO REVERSE THE PROCESS

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

Courtesy SQLAuthority.

answered Mar 1, 2011 at 14:48

IamIC

18.4k20 gold badges96 silver badges162 bronze badges

Collectives™ on Stack Overflow

Working with arrays in SQL Server

4 Answers 4

Comments

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest