[sql] How to pass multiple values to single parameter in stored procedure

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

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

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!

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

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