How to pass multiple values to single parameter in stored procedure

Question

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, ',')                                             )

Mordecai

Can you show us the signature of the stored procedure (what names and datatypes your parameters are), and how you use that parameter @PortfolioId in your stored procedure? – Daisie

Hi marc_s actually i'm using SSRS reporting on report i have dropdown list with all the portfolio names with checkbox. where i can select multiple portfolio at the same time , so it is going to pass to above procedure like this if i'm not wrong. then why i'm getting more no of records instead addition of 5 and 6 portfolios – Mordecai

Answer 1

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.

Nobel

Hi actually i'm using SSRS reporting on report i have dropdown list with all the portfolio names with checkbox. where i can select multiple portfolio at the same time , so it is going to pass to above procedure like this if i'm not wrong. then why i'm getting more no of records instead addition of 5 and 6 portfolios – Mordecai

Are you sure that the parameter is passed as you assume it is? It would be interesting to see the code for the stored procedure in order to answer. – Nobel

Answer 2

I spent time finding a proper way. This may be useful for others.

Create a UDF and refer in the query -

http://www.geekzilla.co.uk/view5C09B52C-4600-4B66-9DD7-DCE840D64CBD.htm

Aksoyn

Soumyendra, perhaps edit your answer, distilling the information in the link, in case the link goes bad in the future? – Vasos

Answer 3

USE THIS

I have had this exact issue for almost 2 weeks, extremely frustrating but I FINALLY found this site and it was a clear walk-through of what to do.

http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/

I hope this helps people because it was exactly what I was looking for

Hypertonic

Wayback machine link as site was down/offline web.archive.org/web/20150825052838/http://blog.summitcloud.com/… – Forcemeat

Answer 4

Either use a User Defined Table

Or you can use CSV by defining your own CSV function as per This Post.

I'd probably recommend the second method, as your stored proc is already written in the correct format and you'll find it handy later on if you need to do this down the road.

Cheers!

Backhanded

Answer 5

I think, below procedure help you to what you are looking for.

 CREATE PROCEDURE [dbo].[FindEmployeeRecord]
        @EmployeeID nvarchar(Max)
    AS
    BEGIN
    DECLARE @sqLQuery VARCHAR(MAX)
    Declare @AnswersTempTable Table
    (  
        EmpId int,         
        EmployeeName nvarchar (250),       
        EmployeeAddress nvarchar (250),  
        PostalCode nvarchar (50),
        TelephoneNo nvarchar (50),
        Email nvarchar (250),
        status nvarchar (50),  
        Sex nvarchar (50) 
    )

    Set @sqlQuery =
    'select e.EmpId,e.EmployeeName,e.Email,e.Sex,ed.EmployeeAddress,ed.PostalCode,ed.TelephoneNo,ed.status
    from Employee e
    join EmployeeDetail ed on e.Empid = ed.iEmpID
    where Convert(nvarchar(Max),e.EmpId) in ('+@EmployeeId+')
    order by EmpId'
    Insert into @AnswersTempTable
    exec (@sqlQuery)
    select * from @AnswersTempTable
    END

Carlacarlee

A link to a potential solution is always welcome, but please add context around the link so your fellow users will have some idea what it is and why it’s there. Always quote the most relevant part of an important link, in case the target site is unreachable or goes permanently offline. Take into account that being barely more than a link to an external site is a possible reason as to Why and how are some answers deleted?. – Afterglow

Also note the same answer was posted (and deleted) almost a year ago. It's alright to link to your own blog or site if it is relevant to the question, but keep in mind it's convenient that an answer stands on its own without needing to go to an external site. It'd also be nice to explicit your relationship to the site if any. – Afterglow

I'd go farther than 'convenient' and say it's a prerequisite for answers to contain enough information to answer the question without having to follow links. Links should be for reference or attribution not the answer itself. – Gotthelf

Answer 6

Create proc sp_firstname(@name varchar(max)) as Begin

Select * from dimemployee Where firstname in(select value from string_split(@name ,’,’))

End

Exec sp_firstname ‘guy,Kevin,Robert’

Digitalize

Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient. Can you kindly edit your answer to offer an explanation? – Mundford