sql server reporting services: Passing multi-select params to a stored procedure -- sql server reporting services

RE: Passing multi-select params to a stored procedure

chabotwvu
4/27/2007 7:16:01 PM

Here 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

[quoted text, click to view]

Re: Passing multi-select params to a stored procedure

Bruce L-C [MVP]
4/29/2007 8:43:33 PM

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

[quoted text, click to view]

Re: Passing multi-select params to a stored procedure

Bruce L-C [MVP]
4/30/2007 12:00:00 AM

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

[quoted text, click to view]

Re: Passing multi-select params to a stored procedure

Duke (AN247)
4/30/2007 6:46:00 AM

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

Re: Passing multi-select params to a stored procedure

Michael C
4/30/2007 8:48:02 AM

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

[quoted text, click to view]

Re: Passing multi-select params to a stored procedure

Michael C
4/30/2007 9:08:02 AM

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

[quoted text, click to view]

Re: Passing multi-select params to a stored procedure

Bruce L-C [MVP]
4/30/2007 11:05:05 AM

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

[quoted text, click to view]

Re: Passing multi-select params to a stored procedure

Bruce L-C [MVP]
4/30/2007 12:13:34 PM

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

[quoted text, click to view]

Re: Passing multi-select params to a stored procedure

Deb
9/5/2007 11:28:00 AM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.