How to pass multiple values to single parameter in stored procedure
reporting-services, sql, sql-server, ssrs-2008
Solution
This can not be done easily. There's no way to make an `NVARCHAR` parameter take "more than one value". What I've done before is - as you do already - make the parameter value like a list with comma-separated values. Then, split this string up into its parts in the stored procedure.
Splitting up can be done using string functions. Add every part to a temporary table. Pseudo-code for this could be:
CREATE TABLE #TempTable (ID INT)
WHILE LEN(@PortfolioID) > 0
BEGIN
IF NOT <@PortfolioID contains Comma>
BEGIN
INSERT INTO #TempTable VALUES CAST(@PortfolioID as INT)
SET @PortfolioID = ''
END ELSE
BEGIN
INSERT INTO #Temptable VALUES CAST(<Part until next comma> AS INT)
SET @PortfolioID = <Everything after the next comma>
END
END
Then, change your condition to
WHERE PortfolioId IN (SELECT ID FROM #TempTable)
EDIT You may be interested in the documentation for multi value parameters in SSRS, which states:
You can define a multivalue parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a data source by using the query, the following requirements must be satisfied:
The data source must be SQL Server, Oracle, Analysis Services, SAP BI NetWeaver, or Hyperion Essbase.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalue parameter array to a stored procedure.
The query must use an IN clause to specify the parameter.
This I found here.
Problem
I'm using SSRS for reporting and executing a stored procedure to generate the data for my reports ``` DECLARE @return_value int EXEC @return_value = [dbo].[MYREPORT] @ComparePeriod = 'Daily', @OverrideCompareDate = NULL, @PortfolioId = '5,6', @OverrideStartDate = NULL, @NewPositionsOnly = NULL, @SourceID = 13 SELECT 'Return Value' = @return_value GO ``` In the above when I passed `@PortfolioId = '5,6'` it is giving me wrong inputs I need all records for `portfolio id 5 and 6 also` is this correct way to send the multiple values ? When I execute my reports only giving `@PortfolioId = '5'` it is giving me 120 records and when I execute it by giving `@PortfolioId = '6'` it is giving me 70 records So when I will give `@PortfolioId = '5,6'` it should have to give me only 190 records altogether, but it is giving me more no of records I don't understand where I exactly go wrong . Could anyone help me? thanks all code is too huge to paste , i'm pasting relevant code please suggest clue. ``` CREATE PROCEDURE [dbo].[GENERATE_REPORT] ( @ComparePeriod VARCHAR(10), @OverrideCompareDate DATETIME, @PortfolioId VARCHAR(50) = '2', --this must be multiple @OverrideStartDate DATETIME = NULL, @NewPositionsOnly BIT = 0, @SourceID INT = NULL ) AS BEGIN SELECT Position.Date, Position.SecurityId, Position.Level1Industry, Position.MoodyFacilityRating, Position.SPFacilityRating, Position.CompositeFacilityRating, Position.SecurityType, Position.FacilityType, Position.Position FROM Fireball_Reporting.dbo.Reporting_DailyNAV_Pricing POSITION WITH (NOLOCK, READUNCOMMITTED) LEFT JOIN Fireball.dbo.AdditionalSecurityPrice ClosingPrice WITH (NOLOCK, READUNCOMMITTED) ON ClosingPrice.SecurityID = Position.PricingSecurityID AND ClosingPrice.Date = Position.Date AND ClosingPrice.SecurityPriceSourceID = @SourceID AND ClosingPrice.PortfolioID IN ( SELECT PARAM FROM Fireball_Reporting.dbo.ParseMultiValuedParameter(@PortfolioId, ',') ) ```