Passing multivalue in stored procedures

Hi guys,

I'm new in SSRS, Im having problems passing multivalue in stored procedures. Can someone help. please.

Thanks in advance.

Hi,

What is the stored procedure expecting? More than one parameter? Or a list of values concatenated with strings?

-Jessica

|||

I want to pass multiple value in the event type. If i pass a single value, it would work but for multiple value it fails.

Here's my storedd procedure:

GO

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATEPROCEDURE [dbo].[usp_retrieve_audit_data]

-- Add the parameters for the stored procedure here

@.start_date asDateTime=NULL,

@.end_date asDateTime=NULL,

@.event asvarchar(20)=NULL

AS

BEGIN

SETNOCOUNTON;

-- Insert statements for procedure here

SELECT audit_log_id, date_created, event_type, event_name, event_status,

row_affected, updated_by

FROM audit_log

WHERE(date_created >= @.start_date)AND(date_created <= @.end_date)AND

(event_type IN(@.event))

END

|||

someone gave me this answer last time... but i've modified the column size

create a table valued function..

CREATEFUNCTION [dbo].[multival_prm]

(@.list ntext,

@.delimiter nchar(1)= N',')

RETURNS @.tbl TABLE(listpos intIDENTITY(1, 1)NOTNULL,

strvarchar(64))AS

BEGIN

DECLARE @.pos int,

@.textpos int,

@.chunklen smallint,

@.tmpstr nvarchar(64),

@.leftover nvarchar(64),

@.tmpval nvarchar(64)

SET @.textpos = 1

SET @.leftover =''

WHILE @.textpos <=datalength(@.list)/ 2

BEGIN

SET @.chunklen = 64 -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)VALUES(@.tmpval)

SET @.tmpstr =substring(@.tmpstr, @.pos + 1,len(@.tmpstr))

SET @.pos =charindex(@.delimiter, @.tmpstr)

END

SET @.leftover = @.tmpstr

END

INSERT @.tbl(str)VALUES(ltrim(rtrim(@.leftover)))

RETURN

END

from your stored proc,

SELECT audit_log_id, date_created, event_type, event_name, event_status,

row_affected, updated_by

FROM audit_log

WHERE(date_created >= @.start_date)AND(date_created <= @.end_date)AND

(event_type inner join multiprm_val(@.event , default))

HTH|||Thanks thanks. I'll try this approach. Then I'll post my solution if its successful.|||opss... i forgotten something... the inner join part..... gotta join both the fields together ....|||

I get this done by useing dynamic SQL. You will need to add an a parm directly to your report.

Here's what your procedure code would look like:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATEPROCEDURE [dbo].[usp_retrieve_audit_data]

@.start_date asDateTime=NULL,

@.end_date asDateTime=NULL,

@.event as varchar(20)=NULL

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.sql varchar(8000), @.crlf char(2)

SELECT @.crlf =char(13)+char(10)

SELECT @.sql =

N'SELECT audit_log_id, date_created, event_type, event_name, event_status, '+ @.crlf

+'row_affected, updated_by'+ @.crlf

+'FROM audit_log'+ @.crlf

+'WHERE (date_created between '''+CONVERT(VARCHAR(30), @.start_date, 121)+''' AND '''+CONVERT(VARCHAR(30), @.end_date, 121)+''') AND'+ @.crlf

+'(event_type IN ('+ @.event +'))'+ @.crlf

EXEC(@.sql)

--PRINT @.sql

END

In you report add a parameter and call it @.Event_List of type string. Make it a multi select and add all of you default values. Wrap the values in single ticks ex. '1'

Hide the parm @.event and under "Default values" set it's "Non-queried" value to the following expression:

=JOIN(Parameters!Event_List, ",")

What is passed back in @.event will be a comma delimited list of values selected in @.Event_List and will look like '1','2','3'

This value is substituted in the dynamic sql above and your dataset will be for only the events selected by your user.