SQL 如何将逗号分隔的列表传递给存储过程？

How to pass a comma separated list to a stored procedure?

提问by naumcho

So I have a Sybase stored proc that takes 1 parameter that's a comma separated list of strings and runs a query with in in an IN() clause:

所以我有一个 Sybase 存储过程，它接受 1 个参数，该参数是一个逗号分隔的字符串列表，并在 IN() 子句中使用 in 运行查询：

CREATE PROCEDURE getSomething @keyList varchar(4096)
AS
SELECT * FROM mytbl WHERE name IN (@keyList)

How do I call my stored proc with more than 1 value in the list? So far I've tried

如何在列表中调用超过 1 个值的存储过程？到目前为止我已经尝试过

exec getSomething 'John'         -- works but only 1 value
exec getSomething 'John','Tom'   -- doesn't work - expects two variables
exec getSomething "'John','Tom'" -- doesn't work - doesn't find anything
exec getSomething '"John","Tom"' -- doesn't work - doesn't find anything
exec getSomething '\'John\',\'Tom\'' -- doesn't work - syntax error

EDIT:I actually found this pagethat has a great reference of the various ways to pas an array to a sproc

编辑：我实际上发现这个页面对将数组传递给 sproc 的各种方法有很好的参考

采纳答案by naumcho

If you're using Sybase 12.5 or earlier then you can't use functions. A workaround might be to populate a temporary table with the values and read them from there.

如果您使用的是 Sybase 12.5 或更早版本，则无法使用函数。解决方法可能是使用值填充临时表并从那里读取它们。

回答by Ben

This is a little late, but I had this exact issue a while ago and I found a solution.

这有点晚了，但我不久前遇到了这个确切的问题，我找到了解决方案。

The trick is double quoting and then wrapping the whole string in quotes.

诀窍是双引号，然后将整个字符串用引号括起来。

exec getSomething """John"",""Tom"",""Bob"",""Harry"""

Modify your proc to match the table entry to the string.

修改您的 proc 以将表条目与字符串匹配。

CREATE PROCEDURE getSomething @keyList varchar(4096)
AS
SELECT * FROM mytbl WHERE @keyList LIKE '%'+name+'%' 

I've had this in production since ASE 12.5; we're now on 15.0.3.

自 ASE 12.5 以来，我已将其投入生产；我们现在在 15.0.3。

回答by Kev

Pass the comma separated list into a function that returns a table value. There is a MS SQL example somewhere on StackOverflow, damned if I can see it at the moment.

将逗号分隔的列表传递到返回表值的函数中。StackOverflow 上的某个地方有一个 MS SQL 示例，如果我现在能看到它，该死的。

CREATE PROCEDURE getSomething @keyList varchar(4096)
AS
SELECT * FROM mytbl WHERE name IN (fn_GetKeyList(@keyList))

CREATE PROCEDURE getSomething @keyList varchar(4096)
AS
SELECT * FROM mytbl WHERE name IN (fn_GetKeyList(@keyList))

Call with -

打电话给 -

exec getSomething 'John,Tom,Foo,Bar'

exec getSomething 'John,Tom,Foo,Bar'

I'm guessing Sybase should be able to do something similar?

我猜 Sybase 应该能够做类似的事情？

回答by Matt Hamilton

Regarding Kevin's idea of passing the parameter to a function that splits the text into a table, here's my implementation of that function from a few years back. Works a treat.

关于 Kevin 将参数传递给将文本拆分为表格的函数的想法，这是我几年前对该函数的实现。很好用。

Splitting Text into Words in SQL

在 SQL 中将文本拆分为单词

回答by Brian Childress

Do you need to use a comma separated list? The last couple of years, I've been taking this type of idea and passing in an XML file. The openxml "function" takes a string and makes it like xml and then if you create a temp table with the data, it is queryable.

你需要使用逗号分隔的列表吗？在过去的几年里，我一直在接受这种类型的想法并传入一个 XML 文件。openxml“函数”接受一个字符串并使其像 xml 一样，然后如果您使用数据创建临时表，则它是可查询的。

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customer',1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))

回答by Brian Childress

This is a quick and dirty method that may be useful:

这是一种可能有用的快速而肮脏的方法：

select  * 
from    mytbl 
where   "," + ltrim(rtrim(@keylist)) + "," like "%," + ltrim(rtrim(name)) + ",%"

回答by Lurker Indeed

Not sure if it's in ASE, but in SQL Anywhere, the sa_split_listfunction returns a table from a CSV. It has optional arguments to pass a different delimiter (default is a comma) and a maxlength for each returned value.

不确定它是否在 ASE 中，但在 SQL Anywhere 中，sa_split_list函数从 CSV 返回一个表。它有可选参数来传递不同的分隔符（默认为逗号）和每个返回值的最大长度。

sa_split_list function

sa_split_list 函数

回答by AdamH

The problem with the calls like this: exec getSomething '"John","Tom"' is that it's treating '"John","Tom"' as a single string, it will only match an entry in the table that is '"John","Tom"'.

像这样调用的问题是： exec getSomething '"John","Tom"' 是它将 '"John","Tom"' 视为单个字符串，它只会匹配表中的一个条目 '"约翰”、“汤姆”'。

If you didn't want to use a temp table as in Paul's answer, then you could use dynamic sql. (Assumes v12+)

如果您不想像 Paul 的回答那样使用临时表，那么您可以使用动态 sql。（假设 v12+）

CREATE PROCEDURE getSomething @keyList varchar(4096)
AS
declare @sql varchar(4096)
select @sql = "SELECT * FROM mytbl WHERE name IN (" + @keyList +")"
exec(@sql)

You will need to ensure the items in @keylist have quotes around them, even if they are single values.

您需要确保 @keylist 中的项目周围有引号，即使它们是单个值。

回答by Abel Gaxiola

This works in SQL. Declare in your GetSomethingprocedure a variable of type XML as such:

这适用于 SQL。在您的GetSomething过程中声明一个 XML 类型的变量，如下所示：

DECLARE @NameArray XML = NULL

The body of the stored procedure implements the following:

存储过程的主体实现以下内容：

SELECT * FROM MyTbl WHERE name IN (SELECT ParamValues.ID.value('.','VARCHAR(10)')
FROM @NameArray.nodes('id') AS ParamValues(ID))

From within the SQL code that calls the SP to declare and initialize the XML variable before calling the stored procedure:

从在调用存储过程之前调用 SP 声明和初始化 XML 变量的 SQL 代码中：

DECLARE @NameArray XML

SET @NameArray = '<id><</id>id>Name_1<<id>/id></id><id><</id>id>Name_2<<id>/id></id><id><</id>id>Name_3<<id>/id></id><id><</id>id>Name_4<<id>/id></id>'

Using your example the call to the stored procedure would be:

使用您的示例，对存储过程的调用将是：

EXEC GetSomething @NameArray

I have used this method before and it works fine. If you want a quick test, copy and paste the following code to a new query and execute:

我以前用过这个方法，效果很好。如果您想要快速测试，请将以下代码复制并粘贴到新查询并执行：

DECLARE @IdArray XML

SET @IdArray = '<id><</id>id>Name_1<<id>/id></id><id><</id>id>Name_2<<id>/id></id><id><</id>id>Name_3<<id>/id></id><id><</id>id>Name_4<<id>/id></id>'

SELECT ParamValues.ID.value('.','VARCHAR(10)')
FROM @IdArray.nodes('id') AS ParamValues(ID)

回答by DeFlanko

To touch on what @Abel provided, what helped me out was:

谈到@Abel 提供的内容，对我有帮助的是：

My purpose was to take what ever the end user inputted from SSRS and use that in my where clause as an In (SELECT) Obviously @ICD_VALUE_RPT would be commented out in my Dataset query.

我的目的是获取最终用户从 SSRS 输入的任何内容，并在我的 where 子句中将其用作 In (SELECT) 显然 @ICD_VALUE_RPT 将在我的数据集查询中被注释掉。

DECLARE @ICD_VALUE_RPT VARCHAR(MAX) SET @ICD_VALUE_RPT = 'Value1, Value2'
DECLARE @ICD_VALUE_ARRAY XML SET @ICD_VALUE_ARRAY = CONCAT('<id>', REPLACE(REPLACE(@ICD_VALUE_RPT, ',', '</id>,<id>'),' ',''), '</id>')

then in my WHEREi added:

然后在我的WHERE我补充说：

(PATS_WITH_PL_DIAGS.ICD10_CODE IN (SELECT ParamValues.ID.value('.','VARCHAR(MAX)') FROM @ICD_VALUE_ARRAY.nodes('id') AS ParamValues(ID))
OR PATS_WITH_PL_DIAGS.ICD9_CODE IN (SELECT ParamValues.ID.value('.','VARCHAR(MAX)') FROM @ICD_VALUE_ARRAY.nodes('id') AS ParamValues(ID))
)