如何将逗号分隔的列表传递给存储过程? [英] How to pass a comma separated list to a stored procedure?

问题描述

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

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:

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

如何调用列表中包含 1 个以上值的存储过程?到目前为止，我已经尝试过

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

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

我实际上发现了这个页面将数组传递给 sproc 的各种方法的参考

I actually found this page that has a great reference of the various ways to pas an array to a sproc

推荐答案

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

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.

这篇关于如何将逗号分隔的列表传递给存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！