Passing Multi Value Delimited string to a parameter

Hi Everyone,
I am using, or want to use a parameter as a filter in a sql statement, the
user will pick from a list that will then send a delimited list of numbers
e.g. "a1,a2,a3,a4" as the parameter that will be referenced in the sql
statement with a " field IN (@.parameter) " type thing.
Im sure this should work and its probably just the phrasing or something
that ive got wrong, can anyone help?
Steve DMulti-select parameters are a feature of RS 2005. This will not work in RS
2000. You can do dynamic sql to do this (note that you do open up to
injection attacks). Switch to generic query designer (to the right of the
...). Put in something like this.
="select somefield from sometable where anotherfield in (" &
Parameters.ParamName.Value & ")"
Note that RS will not detect your field list, so first have a regular query
to populate the field list.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve Dearman" <steve.dearman@.grant.co.uk> wrote in message
news:OFZt89YWGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I am using, or want to use a parameter as a filter in a sql statement, the
> user will pick from a list that will then send a delimited list of numbers
> e.g. "a1,a2,a3,a4" as the parameter that will be referenced in the sql
> statement with a " field IN (@.parameter) " type thing.
> Im sure this should work and its probably just the phrasing or something
> that ive got wrong, can anyone help?
> Steve D
>

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