passing an array into a stored procedure

I am trying to pass a set of id values into a stored procedure.
Currently i am comma seperating these into a varchar to achieve this.
the statement is then executed as follows:
exec 'select * from table where ID in (' + @.VarCharParam + ') Order By
ID'
This dows work fine, but there must be a better way.
Any help would be appreciated
Regards
Grant Merwitz
Hi Grant
The approach you've taken certainly does have it's problems, not the least
of which is that it's subject to SQL injection if it's accessible outside
the DB. Do make sure you understand SQL injection as a minimum before
rolling code like that out.
However, TSQL doesn't have arrays. A common approach to this problem is to
pass in xml either in varchar or text variables which can be opened inside
the stored proc using the sp_xml_preparedocument system proc.
Otherwise, if you're confident you're not subject to injection & you know
you'll only pass in a short list of variables, the approach you've used does
have some merit in that it's light-weight & doesn't varry the overhead of a
few of it's alternatives.
HTH
Regards,
Greg Linwood
SQL Server MVP
"GrantMagic" <grant@.magicalia.com> wrote in message
news:%23PRCD1%23bEHA.3580@.TK2MSFTNGP11.phx.gbl...
> I am trying to pass a set of id values into a stored procedure.
> Currently i am comma seperating these into a varchar to achieve this.
> the statement is then executed as follows:
> exec 'select * from table where ID in (' + @.VarCharParam + ') Order By
> ID'
> This dows work fine, but there must be a better way.
> Any help would be appreciated
> Regards
> Grant Merwitz
>
|||> the statement is then executed as follows:
> exec 'select * from table where ID in (' + @.VarCharParam + ') Order By
> ID'
> This dows work fine, but there must be a better way.
SQL Server doesn't know what an array is. See http://www.aspfaq.com/2248
for an alternative approch, and the links therein for more information.
http://www.aspfaq.com/
(Reverse address to reply.)
|||SQL Server may not know what arrays are, but Erland Sommerskog does :-)
http://www.sommarskog.se/arrays-in-sql.html
It never hurts to set up a table of integers, with a clustered unique index.
One thing SQL Server DOES know how to do is iterate fast through
a set of rows.
You might want to consider the 'monster parameter list' approach.
It works if you can put a reasonable bound (under 1024) on the number
of array elements.
It causes you to generate a lot of repetitive SQL, but once the sproc's
query plan
has been generated, the resulting interpreted code is fast.
CREATE PROC DoThat
@.This varchar(99), @.That varchar(99)
,@.A00 INT=NULL, @.A01 INT=NULL, @.A02 INT=NULL, ...
,@.A10 INT=NULL, @.A11 INT=NULL, @.A12 INT=NULL, ...
...
AS
DECLARE @.A TABLE(val int)
INSERT @.A SELECT *
FROM ( SELECT @.A00 val
UNION ALL SELECT @.A01
UNION ALL SELECT @.A02
...
) X
WHERE val IS NOT NULL
... go wild
If you can't use a default marker like NULL, you need a slightly different
approach:
CREATE PROC DoThat
@.This varchar(99), @.That varchar(99), @.ArgCount INT
,@.A00 INT=NULL, @.A01 INT=NULL, @.A02 INT=NULL, ...
,@.A10 INT=NULL, @.A11 INT=NULL, @.A12 INT=NULL, ...
...
AS
DECLARE @.A TABLE(val int)
INSERT @.A SELECT val
FROM ( SELECT @.A00 val, 00 AS seq
UNION ALL SELECT @.A01, 01
UNION ALL SELECT @.A02, 02
...
) X
WHERE seq < @.ArgCount
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5XVhD$bEHA.2972@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
By
> SQL Server doesn't know what an array is. See http://www.aspfaq.com/2248
> for an alternative approch, and the links therein for more information.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>