blocks|key|3420079|text|您可以创建一个函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3420080|ALTER++FUNCTION+[dbo].[CSVStringsToTable_fn]+(+@array+VARCHAR(8000)+)
RETURNS+@Table+TABLE+(+value+VARCHAR(100)+)
AS+
++++BEGIN
++++++++DECLARE+@separator_position+INTEGER,
++++++++++++@array_value+VARCHAR(8000)++

++++++++SET+@array+=+@array+%2B+','

++++++++WHILE+PATINDEX('%25,%25',+@array)+<>+0+
++++++++++++BEGIN
++++++++++++++++SELECT++@separator_position+=+PATINDEX('%25,%25',+@array)
++++++++++++++++SELECT++@array_value+=+LEFT(@array,+@separator_position+-+1)

++++++++++++++++INSERT++@Table
++++++++++++++++VALUES++(+@array_value+)

++++++++++++++++SELECT++@array+=+STUFF(@array,+1,+@separator_position,+'')
++++++++++++END
++++++++RETURN
++++END|code-block|syntax|javascript|3420081|并从中选择：|3420082|DECLARE+@LocationList+VARCHAR(1000)
SET+@LocationList+=+'1,32'

SELECT++Locations+
FROM++++table
WHERE+++LocationID+IN+(+SELECT+++CAST(value+AS+INT)
+++++++++++++++++++++++++++FROM+++++dbo.CSVStringsToTable_fn(@LocationList)+)|3420083|或|3420084|SELECT++Locations
FROM++++table+loc
++++++++INNER+JOIN+dbo.CSVStringsToTable_fn(@LocationList)+list
++++++++++++ON+CAST(list.value+AS+INT)+=+loc.LocationID|3420085|当您尝试将多值列表从SSRS发送到PROC时，这非常有用。|3420086|CSV编辑：显示您可能需要强制转换-但是要小心控制在列表中发送的内容|offset|length|style|BOLD|3420087|entityMap^0|0|0|0|0|0|0|0|Q|8|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@$S|16|T|17|U|V]]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

You can make a function:

<pre><code>ALTER FUNCTION [dbo].[CSVStringsToTable_fn] ( @array VARCHAR(8000) )
RETURNS @Table TABLE ( value VARCHAR(100) )
AS 
 BEGIN
 DECLARE @separator_position INTEGER,
 @array_value VARCHAR(8000) 

 SET @array = @array + ','

 WHILE PATINDEX('%,%', @array) &lt;&gt; 0 
 BEGIN
 SELECT @separator_position = PATINDEX('%,%', @array)
 SELECT @array_value = LEFT(@array, @separator_position - 1)

 INSERT @Table
 VALUES ( @array_value )

 SELECT @array = STUFF(@array, 1, @separator_position, '')
 END
 RETURN
 END
</code></pre>

and select from it:

<pre><code>DECLARE @LocationList VARCHAR(1000)
SET @LocationList = '1,32'

SELECT Locations 
FROM table
WHERE LocationID IN ( SELECT CAST(value AS INT)
 FROM dbo.CSVStringsToTable_fn(@LocationList) )
</code></pre>

OR

<pre><code>SELECT Locations
FROM table loc
 INNER JOIN dbo.CSVStringsToTable_fn(@LocationList) list
 ON CAST(list.value AS INT) = loc.LocationID
</code></pre>

Which is extremely helpful when you attempt to send a multi-value list from SSRS to a PROC.

Edited: to show that you may need to CAST - However be careful to control what is sent in the CSV list

blocks|key|414590|text|您可以让存储过程接受一个xml类型的输入变量，然后解压元素并获取它们。例如：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|CODE|entityRanges|data|414591|DECLARE+@XMLData+xml

DECLARE+
++++@Code+varchar(10),
++++@Description+varchar(10)

SET+@XMLData+=+
'
++++<SomeCollection>
++++++<SomeItem>
++++++++<Code>ABCD1234</Code>
++++++++<Description>Widget</Description>
++++++</SomeItem>
++++</SomeCollection>
'

SELECT+
++++@Code+=+SomeItems.SomeItem.value('Code[1]',+'varchar(10)'),
++++@Description+=+SomeItems.SomeItem.value('Description[1]',+'varchar(100)')
FROM+@XMLDATA.nodes('//SomeItem')+SomeItems+(SomeItem)

SELECT+@Code+AS+Code,+@Description+AS+Description|code-block|syntax|javascript|414592|结果：|414593|Code++++++++Description
==========++===========
ABCD1234++++Widget|414594|entityMap^0|C|3|C|3|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]|$9|U|A|V|B|D]]|E|@]|F|$]]|$1|G|3|H|5|I|7|W|8|@]|E|@]|F|$J|K]]|$1|L|3|M|5|6|7|X|8|@]|E|@]|F|$]]|$1|N|3|O|5|I|7|Y|8|@]|E|@]|F|$J|K]]|$1|P|3|-4|5|6|7|Z|8|@]|E|@]|F|$]]]|Q|$]]

You can have your sproc take an <code>xml</code> typed input variable, then unpack the elements and grab them. For example:

<pre><code>DECLARE @XMLData xml

DECLARE 
 @Code varchar(10),
 @Description varchar(10)

SET @XMLData = 
'
 &lt;SomeCollection&gt;
 &lt;SomeItem&gt;
 &lt;Code&gt;ABCD1234&lt;/Code&gt;
 &lt;Description&gt;Widget&lt;/Description&gt;
 &lt;/SomeItem&gt;
 &lt;/SomeCollection&gt;
'

SELECT 
 @Code = SomeItems.SomeItem.value('Code[1]', 'varchar(10)'),
 @Description = SomeItems.SomeItem.value('Description[1]', 'varchar(100)')
FROM @XMLDATA.nodes('//SomeItem') SomeItems (SomeItem)

SELECT @Code AS Code, @Description AS Description
</code></pre>

Result:

<pre><code>Code Description
========== ===========
ABCD1234 Widget
</code></pre>

blocks|key|417591|text|有多种方法可以实现这一点，具体方法如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|417592|417593|Passing+CSV将字符串作为(N)VARCHAR参数的参数列出，然后在您的SP中解析它，首先check+here.|ordered-list-item|offset|length|style|BOLD|417594|Create+a+XML+，然后将其作为XML数据类型参数传递。你需要在SP内部解析XML，你可能需要APPLY运算符，在SP外check+here.|417595|Create一个临时表，插入多个值作为多行，这里不需要参数。然后在SP内部使用临时表，check+here.|417596|If您在2008及以上版本尝试使用check+here.|417597|+(表值参数)参数，并将它们作为传递|417598|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlwithmanoj.com/2012/09/09/passing-multipledynamic-values-to-stored-procedures-functions-part1-by-passing-csv/|1|http://sqlwithmanoj.com/2012/09/09/passing-multipledynamic-values-to-stored-procedures-functions-part2-by-passing-xml/|2|http://sqlwithmanoj.com/2012/09/09/passing-multipledynamic-values-to-stored-procedures-functions-part3-by-using-table/|3|http://sqlwithmanoj.com/2012/09/10/passing-multipledynamic-values-to-stored-procedures-functions-part4-by-using-tvp/^0|0|0|0|1O|1D|A|0|0|0|D|1S|A|1|0|8|3|17|A|2|0|H|A|3|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|17|8|@$F|18|G|19|H|I]]|9|@$F|1A|G|1B|1|1C]]|A|$]]|$1|J|3|K|5|E|7|1D|8|@$F|1E|G|1F|H|I]]|9|@$F|1G|G|1H|1|1I]]|A|$]]|$1|L|3|M|5|E|7|1J|8|@$F|1K|G|1L|H|I]]|9|@$F|1M|G|1N|1|1O]]|A|$]]|$1|N|3|O|5|E|7|1P|8|@]|9|@$F|1Q|G|1R|1|1S]]|A|$]]|$1|P|3|Q|5|6|7|1T|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1U|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y]]|Z|$5|U|V|W|A|$X|10]]|11|$5|U|V|W|A|$X|12]]|13|$5|U|V|W|A|$X|14]]]]

There are multiple ways you can achieve this, by:

<ol>
<li>Passing CSV list of strings as an argument to a (N)VARCHAR parameter, then parsing it inside your SP, <a href="http://sqlwithmanoj.com/2012/09/09/passing-multipledynamic-values-to-stored-procedures-functions-part1-by-passing-csv/" rel="nofollow noreferrer">check here</a>.</li>
<li>Create a XML string first of all, then pass it as an XML datatype param. You will need to parse the XML inside the SP, you may need APPLY operator for this, <a href="http://sqlwithmanoj.com/2012/09/09/passing-multipledynamic-values-to-stored-procedures-functions-part2-by-passing-xml/" rel="nofollow noreferrer">check here</a>.</li>
<li>Create a temp table outside the SP, insert the multiple values as multiple rows, no param needed here. Then inside the SP use the temp table, <a href="http://sqlwithmanoj.com/2012/09/09/passing-multipledynamic-values-to-stored-procedures-functions-part3-by-using-table/" rel="nofollow noreferrer">check here</a>.</li>
<li>If you are in 2008 and above try TVPs (Table Valued Parameters) and pass them as params, <a href="http://sqlwithmanoj.com/2012/09/10/passing-multipledynamic-values-to-stored-procedures-functions-part4-by-using-tvp/" rel="nofollow noreferrer">check here</a>.</li>
</ol>

I want to pass multiple values in a single parameter. SQL Server 2005

How to pass multiple values in a single parameter for a stored procedures?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我想在一个参数中传递多个值。SQL Server 2005

问如何在一个存储过程的单个参数中传递多个值？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在一个存储过程的单个参数中传递多个值？EN