kix_eytin


Hi guys,

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

Thanks in advance.




Re: Passing multivalue in stored procedures

JessicaM


Hi,

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

-Jessica






Re: Passing multivalue in stored procedures

kix_eytin

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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_retrieve_audit_data]

-- Add the parameters for the stored procedure here

@start_date as DateTime = NULL,

@end_date as DateTime = NULL,

@event as varchar(20)= NULL

AS

BEGIN

SET NOCOUNT ON;

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






Re: Passing multivalue in stored procedures

minority80

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

create a table valued function..

CREATE FUNCTION [dbo].[multival_prm]

(@list ntext,

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

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(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




Re: Passing multivalue in stored procedures

kix_eytin

Thanks thanks. I'll try this approach. Then I'll post my solution if its successful.



Re: Passing multivalue in stored procedures

minority80

opss... i forgotten something... the inner join part..... gotta join both the fields together ....



Re: Passing multivalue in stored procedures

LightTrainMedia

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

CREATE PROCEDURE [dbo].[usp_retrieve_audit_data]

@start_date as DateTime = NULL,

@end_date as DateTime = 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.