如何将多个值传递给存储过程中的单个参数

I'm using SSRS for reporting and executing a stored procedure to generate the data for my reports

我正在使用SSRS报告和执行存储过程来为我的报告生成数据

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

在上面,当我通过@PortfolioId ='5,6'时,它给了我错误的输入

I need all records for portfolio id 5 and 6 also is this correct way to send the multiple values ?

我需要投资组合ID 5和6的所有记录也是发送多个值的正确方法吗?

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

当我执行我的报告时只给出@PortfolioId ='5'它给了我120条记录,当我通过给@PortfolioId ='6'执行它时它给了我70条记录

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 .

因此,当我给@PortfolioId ='5,6'时,它应该只给我190条记录,但它给了我更多的记录,我不明白我哪里出错了。

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

4 个解决方案

#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.

这不容易做到。无法使NVARCHAR参数采用“多个值”。之前我所做的是 - 正如您已经做的那样 - 将参数值设置为具有逗号分隔值的列表。然后,将此字符串拆分为存储过程中的部分。

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:

编辑您可能对SSRS中多值参数的文档感兴趣,其中指出:

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.

数据源必须是SQL Server,Oracle,Analysis Services,SAP BI NetWeaver或Hyperion Essbase。

The data source cannot be a stored procedure. Reporting Services does not support passing a multivalue parameter array to a stored procedure.

数据源不能是存储过程。 Reporting Services不支持将多值参数数组传递给存储过程。

The query must use an IN clause to specify the parameter.

查询必须使用IN子句指定参数。

This I found here.

这是我在这里找到的。

#2

Either use a User Defined Table

使用用户定义表

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

或者,您可以根据此帖子定义自己的CSV功能来使用CSV。

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!

#3

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

#4

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

我希望这有助于人们,因为它正是我所寻找的