Multi Value Parameters and Stored Procedures

[Roland Müller]

Hi all, I want to submit a MVP from a Report to the SQL Server. 2 Problems:
1: "exec @mvp" does not work because the MVP is recognized as more
parameters due to the Commata in the string

2. The procedure cannot work with an "select * where parameter in (MVP)"
when as string like 'a','b','c' is submitted to the procedure

Does anyone have an idea how to solve this probelm?

Thanks, Roland

[Bruce L-C [MVP]]

What doesn't work has nothing really to do with RS but has to do with Stored

Procedures in SQL Server. You cannot do the following in a stored procedure.

Let's say you have a Parameter called @MyParams

Now you can map that parameter to a multi-value parameter but if in your

stored procedure you try to do this:

select * from sometable where somefield in (@MyParams)

It won't work. Try it. Create a stored procedure and try to pass a

multi-value parameter to the stored procedure. It won't work.

What you can do is to have a string parameter that is passed 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

--

Bruce Loehle-Conger

MVP SQL Server Reporting Services

"Roland Müller" <Rolan...@discussions.microsoft.com> wrote in message
news:89009B54-C1C6-4E0B...@microsoft.com...

[Roland Müller]

Hi Bruce, thanks a lot the procedure works!! But one problem reamains: How to
call the procedure from RS? "exec proc @mvp" results in an error (because
through the commata in the @mvp it is interpreted as more parameters). Is
there a special systax necessary? Thanks, Roland

[Bruce L-C [MVP]]

Going against SQL Server you should be able to select stored procedure as
the type and then just put in the name of the stored procedure. RS
recognizes the parameters of the stored procedure.

If there is not a report parameter named the same it will create one. Then
you just switch the report parameter to multi-value.

I do this and it will work against stored procedures.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Roland Müller" <Rolan...@discussions.microsoft.com> wrote in message

news:1D7C6922-99E4-45A8...@microsoft.com...

[Roland Müller]

Hi Bruce, RS works fine but SQL does not: If I call "exec survey @surveyid,
@questionid, @type, @subtype, @category,0" with all or only one Question ( ->
@questionid) it works. But when I select e.g 3 questions, SQL brings that
message:
"Procedure has to many arguments specified" The parameter @questinid looks
this: "403a,487b,382c". SQL apparently cannot recognize this as one parameter
:-(

[Bruce L-C [MVP]]

How is @questinid declared?

I have a stored procedure with 4 multi-valued parameters (as well as several
other parameters). I declare these parameters as varchar(255)

Again, I am not calling the stored procedure they way you are. It could be
that is what makes a difference.

Go to your dataset declaration. Change the commandtype to stored procedure
and then have this in the pane:

survey

That is it, DO NOT list your parameters. RS automatically gets your
parameters from the stored procedure.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Roland Müller" <Rolan...@discussions.microsoft.com> wrote in message

news:3C7500C0-2E19-4C95...@microsoft.com...

[Roland Müller]

You hero :-) Sorry, but now I have checked it! It works fantastic !!!!!!!

Thanka a lost and best regards,

[ikkyu]

Hello,
I see your solution by using FUNCTION charlist_to_table

I want to have a multi value paramater on AlarmID. I mean user enters the
peiod time, the location and able to select several AlarmID

everything is working OK except that I couldn't do multi para.

can you help me?

but I am confused how do I use it my store proc below

CREATE PROCEDURE [dbo].[PR_AlarmByDurationTest]

(@DDateFr datetime= null,
@DDateTo datetime = null,
@DTimeFr datetime= null,
@DTimeTo datetime= null,
@originatorID int = null,
@AlarmID varchar(1000) = null) -- multi para

AS
BEGIN
SELECT
a.[name]AS AlarmName,a.alarmId,
a.eventTime,a.duration,
CONVERT(varchar,a.eventTime,103) AS Date1, -- dd/mm/yyyy
a.dateOnly,a.timeOnly,
o.systemName,o.systemDescription,a.gatewayName,a.activate

FROM dbo.vw_Alarms a inner join dbo.Originators o on
a.originatorId=o.originatorId

-- I put here Am I correct?
inner join charlist_to_table(@AlarmID,Default)f on a.@AlarmID = f.str --
use function

WHERE
(dateOnly between CONVERT(datetime,@DDateFr,103)AND
CONVERT(datetime,@DDateTo,103))

AND((@DTimeFr is NULL and @DTimeTo is null) OR (a.timeOnly between @DTimeFr
AND @DTimeTo))

AND (@originatorID IS NULL OR a.originatorId = @originatorID)
AND (@AlarmID IS NULL OR alarmId = @AlarmID)

END

[Bruce L-C [MVP]]

No, this is wrong. charlist_to_table is returning a table. You join it just
like any other table.
This: inner join charlist_to_table(@AlarmID,Default)f on a.@AlarmID = f.str

Should be this:
inner join charlist_to_table(@AlarmID,Default)f on a.alarmid = f.str

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"ikkyu" <ik...@discussions.microsoft.com> wrote in message
news:F2DFB500-2A52-4724...@microsoft.com...

[ikkyu]

Thank you Bruce.

It works ok now.