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

我正在使用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

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

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

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

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

谁能帮助我？
谢谢

所有代码都太大而无法粘贴,我粘贴相关代码请提示线索.

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

解决方法

可以使用字符串函数完成拆分.将每个部件添加到临时表中.伪代码可以是：

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

然后,将您的条件更改为

WHERE PortfolioId IN (SELECT ID FROM #TempTable)

编辑
您可能对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.

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.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!