SQL 如何在存储过程中将多个值传递给单个参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13764010/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass multiple values to single parameter in stored procedure
提问by Neo
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 alsois this correct way to send the multiple values ?
我需要所有记录，portfolio id 5 and 6 also这是发送多个值的正确方法吗？
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, ',') )
采纳答案by Thorsten Dittmar
This can not be done easily. There's no way to make an NVARCHARparameter 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.
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.
您可以为您创建的任何报告参数定义多值参数。但是，如果要使用查询将多个参数值传递回数据源，则必须满足以下要求：
数据源必须是 SQL Server、Oracle、Analysis Services、SAP BI NetWeaver 或 Hyperion Essbase。
数据源不能是存储过程。Reporting Services 不支持将多值参数数组传递给存储过程。
查询必须使用 IN 子句来指定参数。
回答by Thorsten Dittmar
Either use a User Defined Table
要么使用用户定义的表
Or you can use CSV by defining your own CSV function as per This Post.
或者您可以通过根据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!
干杯!
回答by gruff
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.
我已经有这个确切的问题将近 2 周了，非常令人沮丧，但我终于找到了这个网站，它清楚地说明了该怎么做。
http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/
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
我希望这对人们有所帮助，因为这正是我正在寻找的
回答by Soumyendra Sharma
I spent time finding a proper way. This may be useful for others.
我花时间寻找合适的方法。这可能对其他人有用。
Create a UDF and refer in the query -
创建一个 UDF 并在查询中引用 -
http://www.geekzilla.co.uk/view5C09B52C-4600-4B66-9DD7-DCE840D64CBD.htm
http://www.geekzilla.co.uk/view5C09B52C-4600-4B66-9DD7-DCE840D64CBD.htm
回答by Hitesh Bhatt
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
