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.
