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

sql sap-ase
naumcho picture naumcho · Aug 8, 2008 · Viewed 46.7k times · Source

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)

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

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

Answer

user3378 picture user3378 · Aug 14, 2008

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.

Related questions

From a Sybase Database, how I can get table description ( field names and types)?

I have access to command line isql and I like to get Meta-Data of all the tables of a given database, possibly in a formatted file. How I can achieve that? Thanks.

Read More
Difference between left join and *= in Sybase ASE 15

I need help to understand this matter, what's the difference between the 2 queries below, all I know that they don't return the same result. Query 1: SELECT a.col1, b.c1 FROM A a LEFT JOIN B b ON a.col1 = …

Read More
How do I get column type from table?

I have this code: select a.id as tableid, a.name as tableName, b.name as columnName, b.status as columnStatus, b.type as columnType from sysobjects a LEFT JOIN syscolumns b ON a.id = b.id WHERE a.name = …

Read More