Passing comma delimited parameter to SP

Is this possible? I find it hard to believe that this could be sooo difficult. I have a simple select stored procedure that has one parameter. My application is passing a comma delimited string of values to be used in the IN clause.

Ex: Where x In(@.parametername)

the x column is an integer. How can one work around this?

Thanks!

http://www.sommarskog.se/arrays-in-sql.html

Passing an IN (a, b, c) list to a sproc as a string -- best method?

I want to do something like this in a stored proc:

--

Create Procedure dbo.GetPatients
@.PatientIdList varchar(200) -- comma separated list of PatientIDs
As

Select *
From Patients
Where PatientId In (@.PatientIdList)

--

I know the above won't work, but of course what I want is if
@.PatientIdList = '1,2,3' then I want Patient records with PatientIds
1, 2, and 3 returned.

It looks like the only way to do this is to build the SQL statement as
a string within the stored procedure ... which pretty much defeats the
usefulness of using precompiled sprocs as I understand it (better off
building a dynamic query against a View in that case).

Thoughts?

Joel Thornton ~ <groups@.joelpt.eml.cc>Joel,

Erland has a decent writing on this topic.

http://www.sommarskog.se/arrays-in-sql.html

--
-oj
http://www.rac4sql.net

"Joel Thornton" <joelpt@.eml.cc> wrote in message
news:c190a45a.0401072012.5c38ba06@.posting.google.c om...
> I want to do something like this in a stored proc:
> --
> Create Procedure dbo.GetPatients
> @.PatientIdList varchar(200) -- comma separated list of PatientIDs
> As
> Select *
> From Patients
> Where PatientId In (@.PatientIdList)
> --
> I know the above won't work, but of course what I want is if
> @.PatientIdList = '1,2,3' then I want Patient records with PatientIds
> 1, 2, and 3 returned.
> It looks like the only way to do this is to build the SQL statement as
> a string within the stored procedure ... which pretty much defeats the
> usefulness of using precompiled sprocs as I understand it (better off
> building a dynamic query against a View in that case).
>
> Thoughts?
> Joel Thornton ~ <groups@.joelpt.eml.ccsql

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

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

passing a comma delimited string to stored procedure

Hello,

I have an asp page that sends a string,
ex. CO,S2,S3,S4,S5,S6,SA,SB,SD,SF,SG,SO,SQ,SR,ST
to a stored procedure in sql server as a single variable
(example @.str).

I want to then somehow split the variable's contents up as 'CO', 'S2', etc to use in a select statement's WHERE IN clause.

Ive tried the replace function to replace the , (comma) with ',' but didnt get the right syntax possibly...

Anybody have any leads or samples done before for this.

Very much appreciated in advance.What about this idea?

drop proc test2
go
create proc test2 @.line varchar(8000)
as
declare @.sql varchar(8000)
select @.sql='select ''ok'' where ''A'' in('+@.line+')'
select @.sql
exec(@.sql)
go
test2 '''A'',''B'',''C'',''D'''
go|||I assume you are going to use this in a dynamic SQL statement?

You say you got a syntax error. Did you remember to put single quotes before and after the string (as snail illustrates), as well as around the commas?

I find it helpful in debugging dynamic code to construct the code in a variable and then PRINT the variable immediately before executing it. If you do this and you are still having problems, post your SQL so that we can review it.

blindman

Passing a comma delimited string of parameters to a stored proc

Hello,

I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.

I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.

But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?

Thanks.

I would recommend Dr. Lisa to you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1

|||

Hi,

You could create a function that would convert your string into a table and then use this table in you stored procedure.

Here is an example of a function that would convert comma separated list into a table:

http://blogs.vandamme.com/development/2007/06/parse_comma_sep.html

Then you could modify your SQL statement in the stored procedure tu use this function, something like this should do:

SELECT <Fields>

FROM <Table> a

JOIN <Function> (@.CommaSeparatedList) b ON b.uid = a.id

HTH,

|||

I usually use MVP Jens Suesmeyer's SPLIT function; it can be found here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

passing a coma delimited group of numbers to a collection for sql

I have a sql statement and one of the arguments I want to pass is a comma delimited set of numbers. It keeps getting turned into a string. How do I keep that from happening. Here is kind of what it looks like
Select FirstName
from User
where NameID in (5,6,7)
or
Select FirstName
from User
where NameID in (@.NameIDList)
There is no error code just nothing returns. If I take out the @.ANameIDList and put the values I want, it returns the correct results.
Thanks,
Bryan
PS the link to the original thread it herehttp://forums.asp.net/1046154/ShowPost.aspxHey,
Right, because unfortunately you can't pass in a list, instead it looks for all the numbers as a string. Instead, you have to create a dynamic string, assign it to a string variable, and do it that way:
declare @.sql varchar(8000)
set @.sql = 'select FirstName from User where NameID in (' + @.NameIDList + ')'
exec(@.sql) -- maybe without parens