April 9, 2010 at 6:13 am
Hi,
i have created one report in which i called stored procedure from sql server.In this procedure i called functions.input parameter to both procedure and function is month.when i selected multivalue parameter in ssrs.when i run this report by selecting 2-3 months then it doesnt retrieve data.for single month selection it executed properly.
i have used in parrmeter in both procedure and function to pass many inputs.
plese anyone can help me in ths?
April 16, 2010 at 4:32 am
Hi
I believe that when you call a Stored procedure with a multivalued parameter with strings they are sent as:
'Jan,feb,Mar,Apr' etc
I think you nedd to parse the parameter in the procedure to get:
'Jan','Feb','Mar','Apr'
to get it to work.
Hope it helps.
Michael
April 19, 2010 at 7:34 am
We had the same problem, so we created the following function to properly convert the parameters...
******************************************************************************
******************************************************************************
USE [InsertDatabaseName]
GO
/*** BEGIN CREATE FUNCTION [Insert Schema Name].[funcParseInputStringToTable] ***/
CREATE FUNCTION [Insert Schema Name].[funcParseInputStringToTable]
(@InputString nvarchar(255))
/* SPECIFY TEMPORARY TABLE*/
RETURNS @InputStringTable TABLE
(InputValue nvarchar(255))
BEGIN
/* DECLARE VARIABLES*/
DECLARE @Index1 AS nvarchar(255),
@ParameterValue AS nvarchar(255)
/* SET VARIABLES */
SET @Index1 = CHARINDEX(',', @InputString)
/* LOOP THROUGH THE INPUT STRING AND IDENTIFY THE INDIVIDUAL VALUES */
WHILE (@Index1 > 0 OR LEN(@InputString) > 0)
BEGIN
/* PARSE OUT EACH INDIVIDUAL PARAMETER AND STORE IN THE TEMPORARY TABLE */
IF @Index1 > 0
BEGIN
SET @ParameterValue = Left(@InputString,@Index1 - 1)
SET @InputString = Right(@InputString,Len(@InputString) - @Index1)
END
ELSE
BEGIN
SET @ParameterValue = @InputString
SET @InputString = ''
END
INSERT @InputStringTable (InputValue)
VALUES(CAST(@ParameterValue AS nvarchar(255)))
/* PREPARE TO LOOP */
SET @Index1 = CHARINDEX(',', @InputString)
END
/* RETURN THE VALUES FROM THE INPUT STRING */
RETURN
END
******************************************************************************
******************************************************************************
Then use the following in your procs:
CREATE PROC [Schema].[ProcName] @InputString [nvarchar](255)
AS
SELECT
[Insert Fields]
FROM
[Insert Table]
WHERE
INSERT [Insert Field] IN (SELECT * FROM funcParseInputStringToTable(@InputString))
END
Hope this helps....
April 20, 2010 at 7:04 am
This can be done easily by using the JOIN built in function in SSRS.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 20, 2010 at 9:55 pm
can you explin JOIN built in function in SSRS?i am not aware about this fn.
April 21, 2010 at 12:06 am
thanks for your help....this table valued function is very mch helpful for me...thnks guys.....
April 21, 2010 at 2:59 am
If you are calling a stored proc in a dataset, it is easy to pass a parameter list from a multivalue parameter by using a JOIN expression.
Lets assume you have a parameter called SalesArea that is a multi value parameter, and you want to pass this to the dataset.
On the window where you define the dataset parameters, specify you want to use an expression to define the parameter value. In the expression window, type the following:
=JOIN(Parameters!SalesArea.Value, ",")
Job done.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 21, 2010 at 3:10 am
nice one, really simple solution, thanks for the update of the example!!
April 21, 2010 at 5:10 am
hey,
im nt getting....this is related to SSRS to pass multiple values.But in database how procedure take the multiple values if i have selected multiple values in report?
In in parameter of procedure we will pass only one value if i want to pass multiple value then i have to use loop.am i wright?
pls help me...im confused.
April 21, 2010 at 6:23 am
The data from your parameter can end up in an IN list in your stored proc.
I do not have an example I can show you today, but may be able to get hold of one tomorrow.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply