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

Question

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

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.

< br > via < a class="StackLink" href=" http://stackoverflow.com/questions/6369/" >How to pass a comma separated list to a stored procedure?< /a>

Share on Google Plus

About Cinema Guy

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.