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

User · Answer

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

User · Answer

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

User · Answer

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   gt  0 BEGIN     IF NOT  lt  PortfolioID contains Comma gt      BEGIN         INSERT INTO  TempTable VALUES CAST  PortfolioID as INT          SET  PortfolioID          END ELSE     BEGIN          INSERT INTO  Temptable VALUES CAST  lt Part until next comma gt  AS INT           SET  PortfolioID    lt Everything after the next comma gt      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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to ssrs-2008

Examples related to reporting-services