Passing multi-select params to a stored procedure

i've already read the MS help file saying this can't be done, but there are
lots of people out there smarter than MS. Has anyone come up with a way to
take a multiple select paramter and pass it into a stored procedure yet? I
noticed there is a built in function string.join which concatonates the
parameters you've chosen, so I'm curious why you could'nt just pass that
string into an "IN" clause. Anyway, let me know if you've got a work around
for this, I'd appreciate it.
MichaelHere is the solution that I came up with. What I did was created a function
that would parse a string based on a delimiter and return the in clause. For
example lets say you were going to pass the string "NY""CA"NC" where the "
symbol is my delimiter into a stored procedure.
Create proc ReturnData
(
@.multivalue varchar(100)
)
as
Decalre @.in varchar(100), @.sqlcmd varchar(500)
set @.in = dbo.RSMultiValue(@.multivalue,'"')
set @.sqlcmd = 'select * from table where state ' + @.in
exec(@.sqlcmd)
The trick is to configure your parameter list correctlly in reporting
services. For example lets say you had a static list of the parameters, what
you want to do is set the value of each parameter value 1 = "NY" parameter
value 2 = "CA" etc...
The string that will be passed = "NY""CA" basically you are just creating
your own list and parsing the data with a function.
I hope this helps
"Michael C" wrote:
> i've already read the MS help file saying this can't be done, but there are
> lots of people out there smarter than MS. Has anyone come up with a way to
> take a multiple select paramter and pass it into a stored procedure yet? I
> noticed there is a built in function string.join which concatonates the
> parameters you've chosen, so I'm curious why you could'nt just pass that
> string into an "IN" clause. Anyway, let me know if you've got a work around
> for this, I'd appreciate it.
> Michael|||You are missing the point about passing multi-select parameters to a stored
procedure. You can easily do this. What you can't do is in the stored
procedure do this:
select * from some table where somefield in (@.MyParam).
The reason has nothing to do with RS. It has to do with stored procedures.
Stored procedures do not allow this because it is dangerous. It leads you
open to security attacks. Again, absolutely nothing to do with RS.
Now, as the other poster suggested you can create your sql on the fly. The
other possibilty is to use a UDF that takes a comma separated list and turns
it into a table variable which you can join with. If you are interested in
the function I can post it for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:97EA5DB7-F6C9-4402-88F3-84315DE094E8@.microsoft.com...
> i've already read the MS help file saying this can't be done, but there
> are
> lots of people out there smarter than MS. Has anyone come up with a way
> to
> take a multiple select paramter and pass it into a stored procedure yet?
> I
> noticed there is a built in function string.join which concatonates the
> parameters you've chosen, so I'm curious why you could'nt just pass that
> string into an "IN" clause. Anyway, let me know if you've got a work
> around
> for this, I'd appreciate it.
> Michael|||Using a user defined function isn't the only way to achive this with SQL
Server.
The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
performance.
Andrew|||Excellent article. It turns out that I got my code from Erland.
Here is the function I use that I got from him: What you can do is to have a
string parameter that is pass ed as a multivalue parameter and then change
the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
"Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> Using a user defined function isn't the only way to achive this with SQL
> Server.
> The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> performance.
> Andrew
>|||Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
going to take me a bit to get my head around this, but let me see if I
understand this:
Basically your passing the entire list of values as a string
(string.join,",") into the function, then creating a temp table in the
function which adds a row for each parameter, and joining my sql statement
inside my stored procedure to this newly created temp table to select the
required rows?
Again, i'm new to this so thanks for your patience and help!
Michael
"Bruce L-C [MVP]" wrote:
> Excellent article. It turns out that I got my code from Erland.
> Here is the function I use that I got from him: What you can do is to have a
> string parameter that is pass ed as a multivalue parameter and then change
> the string into a table.
> This technique was told to me by SQL Server MVP, Erland Sommarskog
> For example I have done this
> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> So note this is NOT an issue with RS, it is strictly a stored procedure
> issue.
> Here is the function:
> CREATE FUNCTION charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> GO
>
> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> > Using a user defined function isn't the only way to achive this with SQL
> > Server.
> >
> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> > performance.
> >
> > Andrew
> >
> >
>
>|||In my example below @.STO was a multi-select parameter. RS automatically is
sending the selection as a comma separated string. You don't need to do
anything to it. Then I pass that to the function I show below. That function
returns a table variable that you can then join to.
Note that you can test this from query analyzer by passing a comma separated
string to your stored procedure.
exec mystoredprocedure 'blah,bleh,etc'
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:B3D41925-5A5A-4827-8786-FF2F50906B88@.microsoft.com...
> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> going to take me a bit to get my head around this, but let me see if I
> understand this:
> Basically your passing the entire list of values as a string
> (string.join,",") into the function, then creating a temp table in the
> function which adds a row for each parameter, and joining my sql statement
> inside my stored procedure to this newly created temp table to select the
> required rows?
> Again, i'm new to this so thanks for your patience and help!
> Michael
>
> "Bruce L-C [MVP]" wrote:
>> Excellent article. It turns out that I got my code from Erland.
>> Here is the function I use that I got from him: What you can do is to
>> have a
>> string parameter that is pass ed as a multivalue parameter and then
>> change
>> the string into a table.
>> This technique was told to me by SQL Server MVP, Erland Sommarskog
>> For example I have done this
>> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
>> So note this is NOT an issue with RS, it is strictly a stored procedure
>> issue.
>> Here is the function:
>> CREATE FUNCTION charlist_to_table
>> (@.list ntext,
>> @.delimiter nchar(1) = N',')
>> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> str varchar(4000),
>> nstr nvarchar(2000)) AS
>> BEGIN
>> DECLARE @.pos int,
>> @.textpos int,
>> @.chunklen smallint,
>> @.tmpstr nvarchar(4000),
>> @.leftover nvarchar(4000),
>> @.tmpval nvarchar(4000)
>> SET @.textpos = 1
>> SET @.leftover = ''
>> WHILE @.textpos <= datalength(@.list) / 2
>> BEGIN
>> SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
>> SET @.textpos = @.textpos + @.chunklen
>> SET @.pos = charindex(@.delimiter, @.tmpstr)
>> WHILE @.pos > 0
>> BEGIN
>> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> SET @.pos = charindex(@.delimiter, @.tmpstr)
>> END
>> SET @.leftover = @.tmpstr
>> END
>> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> ltrim(rtrim(@.leftover)))
>> RETURN
>> END
>> GO
>>
>> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
>> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
>> > Using a user defined function isn't the only way to achive this with
>> > SQL
>> > Server.
>> >
>> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
>> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity
>> > for
>> > performance.
>> >
>> > Andrew
>> >
>> >
>>|||So question... Here is my code. Where would I insert this? the @.Mill, @.Port
can be mulitples (the @.Trip is always a single parameter)
ALTER PROCEDURE [dbo].[LumberLineUp]
@.Mill varchar(100) = '0',
@.Trip varchar(100) = '0',
@.Port varchar(100) = '0'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- INITIAL TABLES
CREATE TABLE [dbo].[#LumberLineUp](
[Vessel] [nvarchar](50) NULL,
[LoadPort][nvarchar](50) NULL,
[Trip][nvarchar](50) NULL,
[OrderID][nvarchar](20) NOT NULL,
[Mill][nvarchar](50) NULL,
[Mark][nvarchar](20) NULL,
[Product][nvarchar](160) NULL,
[Parent][nvarchar](160) NULL,
[FBM][real]NULL,
[Pkgs][real]NULL,
[Stowage][nvarchar](5) NULL,
[Grade][nvarchar](10) NULL,
[Length][nvarchar](10)NULL,
[LengthDesc][nvarchar](10) NULL,
[Port][nvarchar](50)NULL,
[LoadDate][nvarchar](50)NULL,
[ETADate][nvarchar](50)NULL
)
CREATE TABLE [dbo].[#LengthTally](
[OrderID] [varchar](20) NULL,
[Mark] [varchar](20) NULL,
[Tally] [varchar](120) NULL,
[Prod] [varchar](160)NULL,
[OrderPrev] [varchar](20) NULL,
[MarkPrev] [varchar](20) NULL,
[ProductPrev] [varchar](160)NULL,
[Length] [nvarchar](10) NULL,
[LengthPrev] [nvarchar](10) NULL,
[Count] [varchar](10) NULL,
[Test][varchar](10) NULL
)
-- Data for LumberLineUp Table
INSERT INTO dbo.#LumberLineUp
SELECT Vessel.Description, LoadPorts.Description AS LoadPort,
VesselTrips.ID AS Trip, order_header.order_id AS OrderID,
location.location_id AS Mill,
order_detail.Mark, product_master.descrip AS Product,
product_parent.descrip AS parent,
CASE order_detail.qty_unit WHEN 'm3' THEN
order_detail.qty * product_master.m3_per_lin WHEN 'FBM' THEN order_detail.qty
END AS FBM,
order_detail.alt_qty AS Pkgs, order_detail.Stowage,
product_master.grade, length_master.sort_fact AS Length,
length_master.descrip AS Legnth,
port.port_id AS Port, VesselTrips.LoadingDate,
tripdestinations.ETADate
FROM Vessel AS Vessel INNER JOIN
VesselVoyages AS VesselVoyages ON
VesselVoyages.Vessel_ID = Vessel.ID INNER JOIN
VesselTrips AS VesselTrips ON
VesselTrips.VesselVoyage_ID = VesselVoyages.ID INNER JOIN
order_header AS order_header ON
order_header.VesselTrip_ID = VesselTrips.ID INNER JOIN
location AS location ON location.location_id =order_header.location INNER JOIN
order_detail AS order_detail ON order_detail.order_id
= order_header.order_id INNER JOIN
product_master AS product_master ON
product_master.product_id = order_detail.product_id INNER JOIN
product_master AS product_parent ON
product_master.product_part = product_parent.product_id INNER JOIN
length_master AS length_master ON
product_master.length_part = length_master.len_id INNER JOIN
TripDestinations AS tripdestinations ON
tripdestinations.Trip_ID = order_header.VesselTrip_ID AND
tripdestinations.Port_ID =order_header.Destination_Port INNER JOIN
port AS port ON tripdestinations.Port_ID =port.port_id INNER JOIN
LoadPorts ON VesselTrips.LoadPort = LoadPorts.ID
WHERE (port.port_id IN (@.Port)) AND (location.location_id IN (@.Mill))
AND (VesselTrips.ID IN (@.Trip)) OR
(location.location_id IN (@.Mill)) AND (VesselTrips.ID
IN (@.Trip)) AND (@.Port = '0')
ORDER BY OrderID, order_detail.Mark, Product, Length
"Michael C" wrote:
> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> going to take me a bit to get my head around this, but let me see if I
> understand this:
> Basically your passing the entire list of values as a string
> (string.join,",") into the function, then creating a temp table in the
> function which adds a row for each parameter, and joining my sql statement
> inside my stored procedure to this newly created temp table to select the
> required rows?
> Again, i'm new to this so thanks for your patience and help!
> Michael
>
> "Bruce L-C [MVP]" wrote:
> > Excellent article. It turns out that I got my code from Erland.
> >
> > Here is the function I use that I got from him: What you can do is to have a
> > string parameter that is pass ed as a multivalue parameter and then change
> > the string into a table.
> > This technique was told to me by SQL Server MVP, Erland Sommarskog
> >
> > For example I have done this
> >
> > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> >
> > So note this is NOT an issue with RS, it is strictly a stored procedure
> > issue.
> >
> > Here is the function:
> >
> > CREATE FUNCTION charlist_to_table
> >
> > (@.list ntext,
> >
> > @.delimiter nchar(1) = N',')
> >
> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> >
> > str varchar(4000),
> >
> > nstr nvarchar(2000)) AS
> >
> > BEGIN
> >
> > DECLARE @.pos int,
> >
> > @.textpos int,
> >
> > @.chunklen smallint,
> >
> > @.tmpstr nvarchar(4000),
> >
> > @.leftover nvarchar(4000),
> >
> > @.tmpval nvarchar(4000)
> >
> > SET @.textpos = 1
> >
> > SET @.leftover = ''
> >
> > WHILE @.textpos <= datalength(@.list) / 2
> >
> > BEGIN
> >
> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> >
> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> >
> > SET @.textpos = @.textpos + @.chunklen
> >
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >
> > WHILE @.pos > 0
> >
> > BEGIN
> >
> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> >
> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> >
> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> >
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >
> > END
> >
> > SET @.leftover = @.tmpstr
> >
> > END
> >
> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> >
> > ltrim(rtrim(@.leftover)))
> >
> > RETURN
> >
> > END
> >
> > GO
> >
> >
> >
> > "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> > news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> > > Using a user defined function isn't the only way to achive this with SQL
> > > Server.
> > >
> > > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> > > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> > > performance.
> > >
> > > Andrew
> > >
> > >
> >
> >
> >|||Add another inner join with @.port
inner join charlist_to_table(@.port,Default)f on port.port_id = f.str
remove (port.port_id IN (@.Port))
Follow the same pattern as above with your other multi-value parameter
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:48471539-1EA0-489C-B3F6-85240B1FE04E@.microsoft.com...
> So question... Here is my code. Where would I insert this? the @.Mill,
> @.Port
> can be mulitples (the @.Trip is always a single parameter)
> ALTER PROCEDURE [dbo].[LumberLineUp]
> @.Mill varchar(100) = '0',
> @.Trip varchar(100) = '0',
> @.Port varchar(100) = '0'
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- INITIAL TABLES
> CREATE TABLE [dbo].[#LumberLineUp](
> [Vessel] [nvarchar](50) NULL,
> [LoadPort][nvarchar](50) NULL,
> [Trip][nvarchar](50) NULL,
> [OrderID][nvarchar](20) NOT NULL,
> [Mill][nvarchar](50) NULL,
> [Mark][nvarchar](20) NULL,
> [Product][nvarchar](160) NULL,
> [Parent][nvarchar](160) NULL,
> [FBM][real]NULL,
> [Pkgs][real]NULL,
> [Stowage][nvarchar](5) NULL,
> [Grade][nvarchar](10) NULL,
> [Length][nvarchar](10)NULL,
> [LengthDesc][nvarchar](10) NULL,
> [Port][nvarchar](50)NULL,
> [LoadDate][nvarchar](50)NULL,
> [ETADate][nvarchar](50)NULL
> )
> CREATE TABLE [dbo].[#LengthTally](
> [OrderID] [varchar](20) NULL,
> [Mark] [varchar](20) NULL,
> [Tally] [varchar](120) NULL,
> [Prod] [varchar](160)NULL,
> [OrderPrev] [varchar](20) NULL,
> [MarkPrev] [varchar](20) NULL,
> [ProductPrev] [varchar](160)NULL,
> [Length] [nvarchar](10) NULL,
> [LengthPrev] [nvarchar](10) NULL,
> [Count] [varchar](10) NULL,
> [Test][varchar](10) NULL
> )
> -- Data for LumberLineUp Table
> INSERT INTO dbo.#LumberLineUp
> SELECT Vessel.Description, LoadPorts.Description AS LoadPort,
> VesselTrips.ID AS Trip, order_header.order_id AS OrderID,
> location.location_id AS Mill,
> order_detail.Mark, product_master.descrip AS Product,
> product_parent.descrip AS parent,
> CASE order_detail.qty_unit WHEN 'm3' THEN
> order_detail.qty * product_master.m3_per_lin WHEN 'FBM' THEN
> order_detail.qty
> END AS FBM,
> order_detail.alt_qty AS Pkgs, order_detail.Stowage,
> product_master.grade, length_master.sort_fact AS Length,
> length_master.descrip AS Legnth,
> port.port_id AS Port, VesselTrips.LoadingDate,
> tripdestinations.ETADate
> FROM Vessel AS Vessel INNER JOIN
> VesselVoyages AS VesselVoyages ON
> VesselVoyages.Vessel_ID = Vessel.ID INNER JOIN
> VesselTrips AS VesselTrips ON
> VesselTrips.VesselVoyage_ID = VesselVoyages.ID INNER JOIN
> order_header AS order_header ON
> order_header.VesselTrip_ID = VesselTrips.ID INNER JOIN
> location AS location ON location.location_id => order_header.location INNER JOIN
> order_detail AS order_detail ON order_detail.order_id
> = order_header.order_id INNER JOIN
> product_master AS product_master ON
> product_master.product_id = order_detail.product_id INNER JOIN
> product_master AS product_parent ON
> product_master.product_part = product_parent.product_id INNER JOIN
> length_master AS length_master ON
> product_master.length_part = length_master.len_id INNER JOIN
> TripDestinations AS tripdestinations ON
> tripdestinations.Trip_ID = order_header.VesselTrip_ID AND
> tripdestinations.Port_ID => order_header.Destination_Port INNER JOIN
> port AS port ON tripdestinations.Port_ID => port.port_id INNER JOIN
> LoadPorts ON VesselTrips.LoadPort = LoadPorts.ID
> WHERE (port.port_id IN (@.Port)) AND (location.location_id IN (@.Mill))
> AND (VesselTrips.ID IN (@.Trip)) OR
> (location.location_id IN (@.Mill)) AND (VesselTrips.ID
> IN (@.Trip)) AND (@.Port = '0')
> ORDER BY OrderID, order_detail.Mark, Product, Length
>
> "Michael C" wrote:
>> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
>> going to take me a bit to get my head around this, but let me see if I
>> understand this:
>> Basically your passing the entire list of values as a string
>> (string.join,",") into the function, then creating a temp table in the
>> function which adds a row for each parameter, and joining my sql
>> statement
>> inside my stored procedure to this newly created temp table to select the
>> required rows?
>> Again, i'm new to this so thanks for your patience and help!
>> Michael
>>
>> "Bruce L-C [MVP]" wrote:
>> > Excellent article. It turns out that I got my code from Erland.
>> >
>> > Here is the function I use that I got from him: What you can do is to
>> > have a
>> > string parameter that is pass ed as a multivalue parameter and then
>> > change
>> > the string into a table.
>> > This technique was told to me by SQL Server MVP, Erland Sommarskog
>> >
>> > For example I have done this
>> >
>> > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
>> >
>> > So note this is NOT an issue with RS, it is strictly a stored procedure
>> > issue.
>> >
>> > Here is the function:
>> >
>> > CREATE FUNCTION charlist_to_table
>> >
>> > (@.list ntext,
>> >
>> > @.delimiter nchar(1) = N',')
>> >
>> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> >
>> > str varchar(4000),
>> >
>> > nstr nvarchar(2000)) AS
>> >
>> > BEGIN
>> >
>> > DECLARE @.pos int,
>> >
>> > @.textpos int,
>> >
>> > @.chunklen smallint,
>> >
>> > @.tmpstr nvarchar(4000),
>> >
>> > @.leftover nvarchar(4000),
>> >
>> > @.tmpval nvarchar(4000)
>> >
>> > SET @.textpos = 1
>> >
>> > SET @.leftover = ''
>> >
>> > WHILE @.textpos <= datalength(@.list) / 2
>> >
>> > BEGIN
>> >
>> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> >
>> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos,
>> > @.chunklen)
>> >
>> > SET @.textpos = @.textpos + @.chunklen
>> >
>> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> >
>> > WHILE @.pos > 0
>> >
>> > BEGIN
>> >
>> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> >
>> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> >
>> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> >
>> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> >
>> > END
>> >
>> > SET @.leftover = @.tmpstr
>> >
>> > END
>> >
>> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> >
>> > ltrim(rtrim(@.leftover)))
>> >
>> > RETURN
>> >
>> > END
>> >
>> > GO
>> >
>> >
>> >
>> > "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
>> > news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
>> > > Using a user defined function isn't the only way to achive this with
>> > > SQL
>> > > Server.
>> > >
>> > > The article http://www.sommarskog.se/arrays-in-sql-2005.html by
>> > > Erland
>> > > Sommarskog (SQL Server MVP) has a few alternatives trading
>> > > simplicity for
>> > > performance.
>> > >
>> > > Andrew
>> > >
>> > >
>> >
>> >
>> >|||Hello,
This works perfectly for me. I was wondering if there is a way to
incorporate the parameter "All" to select the entire list?
Thanks,
Deb
"Bruce L-C [MVP]" wrote:
> In my example below @.STO was a multi-select parameter. RS automatically is
> sending the selection as a comma separated string. You don't need to do
> anything to it. Then I pass that to the function I show below. That function
> returns a table variable that you can then join to.
> Note that you can test this from query analyzer by passing a comma separated
> string to your stored procedure.
> exec mystoredprocedure 'blah,bleh,etc'
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
> news:B3D41925-5A5A-4827-8786-FF2F50906B88@.microsoft.com...
> > Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> > going to take me a bit to get my head around this, but let me see if I
> > understand this:
> >
> > Basically your passing the entire list of values as a string
> > (string.join,",") into the function, then creating a temp table in the
> > function which adds a row for each parameter, and joining my sql statement
> > inside my stored procedure to this newly created temp table to select the
> > required rows?
> >
> > Again, i'm new to this so thanks for your patience and help!
> >
> > Michael
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Excellent article. It turns out that I got my code from Erland.
> >>
> >> Here is the function I use that I got from him: What you can do is to
> >> have a
> >> string parameter that is pass ed as a multivalue parameter and then
> >> change
> >> the string into a table.
> >> This technique was told to me by SQL Server MVP, Erland Sommarskog
> >>
> >> For example I have done this
> >>
> >> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> >>
> >> So note this is NOT an issue with RS, it is strictly a stored procedure
> >> issue.
> >>
> >> Here is the function:
> >>
> >> CREATE FUNCTION charlist_to_table
> >>
> >> (@.list ntext,
> >>
> >> @.delimiter nchar(1) = N',')
> >>
> >> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> >>
> >> str varchar(4000),
> >>
> >> nstr nvarchar(2000)) AS
> >>
> >> BEGIN
> >>
> >> DECLARE @.pos int,
> >>
> >> @.textpos int,
> >>
> >> @.chunklen smallint,
> >>
> >> @.tmpstr nvarchar(4000),
> >>
> >> @.leftover nvarchar(4000),
> >>
> >> @.tmpval nvarchar(4000)
> >>
> >> SET @.textpos = 1
> >>
> >> SET @.leftover = ''
> >>
> >> WHILE @.textpos <= datalength(@.list) / 2
> >>
> >> BEGIN
> >>
> >> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> >>
> >> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> >>
> >> SET @.textpos = @.textpos + @.chunklen
> >>
> >> SET @.pos = charindex(@.delimiter, @.tmpstr)
> >>
> >> WHILE @.pos > 0
> >>
> >> BEGIN
> >>
> >> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> >>
> >> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> >>
> >> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> >>
> >> SET @.pos = charindex(@.delimiter, @.tmpstr)
> >>
> >> END
> >>
> >> SET @.leftover = @.tmpstr
> >>
> >> END
> >>
> >> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> >>
> >> ltrim(rtrim(@.leftover)))
> >>
> >> RETURN
> >>
> >> END
> >>
> >> GO
> >>
> >>
> >>
> >> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> >> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> >> > Using a user defined function isn't the only way to achive this with
> >> > SQL
> >> > Server.
> >> >
> >> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> >> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity
> >> > for
> >> > performance.
> >> >
> >> > Andrew
> >> >
> >> >
> >>
> >>
> >>
>
>sql