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

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存储的proc它有一个参数，它是一个逗号分隔的字符串列表，运行一个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的存储proc ?到目前为止,我已经试过

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 page that has a great reference of the various ways to pas an array to a sproc

编辑:我实际上找到了这个页面，它很好地参考了各种将数组应用到sproc的方法

11 个解决方案

#1

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或更早的版本，那么就不能使用函数。一个变通方法可能是用值填充一个临时表，并从那里读取它们。

#2

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。

#3

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

将逗号分隔的列表传递给返回表值的函数。在*上有一个MS SQL的例子，如果我现在能看到它，那就见鬼去吧。

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

Call with -

电话-

exec getSomething 'John,Tom,Foo,Bar'

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

我猜Sybase应该可以做类似的事情?

#4

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

#5

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语句。

#6

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

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

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

#7

Not sure if it's in ASE, but in SQL Anywhere, the sa_split_list function 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中，sa_split_list函数从CSV返回一个表。它有可选参数来传递不同的分隔符(默认是逗号)和每个返回值的maxlength。

sa_split_list function

sa_split_list函数

#8

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获取一些“John”、“Tom”，它将“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中的项目周围有引号，即使它们是单个值。

#9

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

这是在SQL。在GetSomething过程中声明一个XML类型的变量:

DECLARE @NameArray XML = NULL

声明@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))

从@NameArray.nodes('id')中选择*，其中name(选择ParamValues.ID.value('.'，'VARCHAR(10)')

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

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

DECLARE @NameArray XML

声明@NameArray XML

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

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

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

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

EXEC GetSomething @NameArray

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

声明@IdArray XML

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

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

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

从@IdArray.nodes(“id”)中选择ParamValues.ID.value('.'，'VARCHAR(10)')作为ParamValues(id)

#10

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会在我的Dataset查询中注释掉。

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 WHERE i added:

然后在我的地方，我补充说:

(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))
)

#11

Try this way. Its works for me.

尝试这种方式。它为我工作。

@itemIds varchar(max)

CREATE PROCEDURE getSomething @keyList varchar(4096)
AS
SELECT * FROM mytbl WHERE name IN (SELECT Value FROM [Global_Split] (@itemIds,','))