<p>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:</p>

<pre><code>CREATE PROCEDURE getSomething @keyList varchar(4096)
AS
SELECT * FROM mytbl WHERE name IN (@keyList)
</code></pre>

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

<pre><code>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
</code></pre>

<p><strong>EDIT:</strong> I actually found this <a href="http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm" rel="noreferrer">page</a> that has a great reference of the various ways to pas an array to a sproc</p>

[{'answer_id': 6377, 'author': 'Brian Childress', 'author_id': 721, 'author_profile': 'https://Stackoverflow.com/users/721', 'pm_score': 0, 'selected': False, 'text': '<p>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.</p>\n\n<pre><code>DECLARE @idoc int\nDECLARE @doc varchar(1000)\nSET @doc =\'\n&lt;ROOT&gt;\n&lt;Customer CustomerID="VINET" ContactName="Paul Henriot"&gt;\n   &lt;Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"&gt;\n      &lt;OrderDetail OrderID="10248" ProductID="11" Quantity="12"/&gt;\n      &lt;OrderDetail OrderID="10248" ProductID="42" Quantity="10"/&gt;\n   &lt;/Order&gt;\n&lt;/Customer&gt;\n&lt;Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"&gt;\n   &lt;Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"&gt;\n      &lt;OrderDetail OrderID="10283" ProductID="72" Quantity="3"/&gt;\n   &lt;/Order&gt;\n&lt;/Customer&gt;\n&lt;/ROOT&gt;\'\n--Create an internal representation of the XML document.\nEXEC sp_xml_preparedocument @idoc OUTPUT, @doc\n-- Execute a SELECT statement that uses the OPENXML rowset provider.\nSELECT    *\nFROM       OPENXML (@idoc, \'/ROOT/Customer\',1)\n            WITH (CustomerID  varchar(10),\n                  ContactName varchar(20))\n</code></pre>\n'}, {'answer_id': 6384, 'author': 'Kev', 'author_id': 419, 'author_profile': 'https://Stackoverflow.com/users/419', 'pm_score': 1, 'selected': False, 'text': "<p>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.</p>\n\n<blockquote>\n<pre><code>CREATE PROCEDURE getSomething @keyList varchar(4096)\nAS\nSELECT * FROM mytbl WHERE name IN (fn_GetKeyList(@keyList))\n</code></pre>\n</blockquote>\n\n<p>Call with -</p>\n\n<blockquote>\n<pre><code>exec getSomething 'John,Tom,Foo,Bar'\n</code></pre>\n</blockquote>\n\n<p>I'm guessing Sybase should be able to do something similar?</p>\n"}, {'answer_id': 6483, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 0, 'selected': False, 'text': '<p>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.</p>\n\n<p><a href="http://www.madprops.org/blog/splitting-text-into-words-in-sql-revisited/" rel="nofollow noreferrer">Splitting Text into Words in SQL</a></p>\n'}, {'answer_id': 10838, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': True, 'text': "<p>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.</p>\n"}, {'answer_id': 48422, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>This is a quick and dirty method that may be useful:</p>\n\n<pre><code>select  * \nfrom    mytbl \nwhere   "," + ltrim(rtrim(@keylist)) + "," like "%," + ltrim(rtrim(name)) + ",%"\n</code></pre>\n'}, {'answer_id': 123593, 'author': 'Lurker Indeed', 'author_id': 16951, 'author_profile': 'https://Stackoverflow.com/users/16951', 'pm_score': 0, 'selected': False, 'text': '<p>Not sure if it\'s in ASE, but in SQL Anywhere, the <em>sa_split_list</em> 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.</p>\n\n<p><a href="http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1000/en/html/dbrfen10/rf-sa-set-http-option.html" rel="nofollow noreferrer">sa_split_list function</a></p>\n'}, {'answer_id': 123686, 'author': 'AdamH', 'author_id': 21081, 'author_profile': 'https://Stackoverflow.com/users/21081', 'pm_score': 0, 'selected': False, 'text': '<p>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"\'.</p>\n\n<p>If you didn\'t want to use a temp table as in Paul\'s answer, then you could use dynamic sql. (Assumes v12+)</p>\n\n<pre><code>CREATE PROCEDURE getSomething @keyList varchar(4096)\nAS\ndeclare @sql varchar(4096)\nselect @sql = "SELECT * FROM mytbl WHERE name IN (" + @keyList +")"\nexec(@sql)\n</code></pre>\n\n<p>You will need to ensure the items in @keylist have quotes around them, even if they are single values.</p>\n'}, {'answer_id': 251136, 'author': 'Abel Gaxiola', 'author_id': 31191, 'author_profile': 'https://Stackoverflow.com/users/31191', 'pm_score': 0, 'selected': False, 'text': "<p>This works in SQL.  Declare in your <code>GetSomething</code> procedure a variable of type XML as such:</p>\n\n<pre><code>DECLARE @NameArray XML = NULL\n</code></pre>\n\n<p>The body of the stored procedure implements the following:</p>\n\n<pre><code>SELECT * FROM MyTbl WHERE name IN (SELECT ParamValues.ID.value('.','VARCHAR(10)')\nFROM @NameArray.nodes('id') AS ParamValues(ID))\n</code></pre>\n\n<p>From within the SQL code that calls the SP to declare and initialize the XML variable before calling the stored procedure:</p>\n\n<pre><code>DECLARE @NameArray XML\n\nSET @NameArray = '&lt;id&gt;&lt;&lt;/id&gt;id&gt;Name_1&lt;&lt;id&gt;/id&gt;&lt;/id&gt;&lt;id&gt;&lt;&lt;/id&gt;id&gt;Name_2&lt;&lt;id&gt;/id&gt;&lt;/id&gt;&lt;id&gt;&lt;&lt;/id&gt;id&gt;Name_3&lt;&lt;id&gt;/id&gt;&lt;/id&gt;&lt;id&gt;&lt;&lt;/id&gt;id&gt;Name_4&lt;&lt;id&gt;/id&gt;&lt;/id&gt;'\n</code></pre>\n\n<p>Using your example the call to the stored procedure would be:</p>\n\n<pre><code>EXEC GetSomething @NameArray\n</code></pre>\n\n<p>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:</p>\n\n<pre><code>DECLARE @IdArray XML\n\nSET @IdArray = '&lt;id&gt;&lt;&lt;/id&gt;id&gt;Name_1&lt;&lt;id&gt;/id&gt;&lt;/id&gt;&lt;id&gt;&lt;&lt;/id&gt;id&gt;Name_2&lt;&lt;id&gt;/id&gt;&lt;/id&gt;&lt;id&gt;&lt;&lt;/id&gt;id&gt;Name_3&lt;&lt;id&gt;/id&gt;&lt;/id&gt;&lt;id&gt;&lt;&lt;/id&gt;id&gt;Name_4&lt;&lt;id&gt;/id&gt;&lt;/id&gt;'\n\nSELECT ParamValues.ID.value('.','VARCHAR(10)')\nFROM @IdArray.nodes('id') AS ParamValues(ID)\n</code></pre>\n"}, {'answer_id': 7892156, 'author': 'Ben', 'author_id': 982820, 'author_profile': 'https://Stackoverflow.com/users/982820', 'pm_score': 2, 'selected': False, 'text': '<p>This is a little late, but I had this exact issue a while ago and I found a solution.</p>\n\n<p>The trick is double quoting and then wrapping the whole string in quotes.</p>\n\n<pre><code>exec getSomething """John"",""Tom"",""Bob"",""Harry"""\n</code></pre>\n\n<p>Modify your proc to match the table entry to the string. </p>\n\n<pre><code>CREATE PROCEDURE getSomething @keyList varchar(4096)\nAS\nSELECT * FROM mytbl WHERE @keyList LIKE \'%\'+name+\'%\' \n</code></pre>\n\n<p>I\'ve had this in production since ASE 12.5; we\'re now on 15.0.3.</p>\n'}, {'answer_id': 37604460, 'author': 'DeFlanko', 'author_id': 4006015, 'author_profile': 'https://Stackoverflow.com/users/4006015', 'pm_score': 0, 'selected': False, 'text': "<p>To touch on what @Abel provided, what helped me out was:</p>\n\n<p>My purpose was to take what ever the end user inputted from SSRS and use that in my where clause as an In (SELECT)\nObviously @ICD_VALUE_RPT would be commented out in my Dataset query. </p>\n\n<pre><code>DECLARE @ICD_VALUE_RPT VARCHAR(MAX) SET @ICD_VALUE_RPT = 'Value1, Value2'\nDECLARE @ICD_VALUE_ARRAY XML SET @ICD_VALUE_ARRAY = CONCAT('&lt;id&gt;', REPLACE(REPLACE(@ICD_VALUE_RPT, ',', '&lt;/id&gt;,&lt;id&gt;'),' ',''), '&lt;/id&gt;')\n</code></pre>\n\n<p>then in my <code>WHERE</code> i added:</p>\n\n<pre><code>(PATS_WITH_PL_DIAGS.ICD10_CODE IN (SELECT ParamValues.ID.value('.','VARCHAR(MAX)') FROM @ICD_VALUE_ARRAY.nodes('id') AS ParamValues(ID))\nOR PATS_WITH_PL_DIAGS.ICD9_CODE IN (SELECT ParamValues.ID.value('.','VARCHAR(MAX)') FROM @ICD_VALUE_ARRAY.nodes('id') AS ParamValues(ID))\n)\n</code></pre>\n"}, {'answer_id': 46092262, 'author': 'user3732708', 'author_id': 3732708, 'author_profile': 'https://Stackoverflow.com/users/3732708', 'pm_score': 0, 'selected': False, 'text': "<p>Try this way. Its works for me.</p>\n\n<pre><code>@itemIds varchar(max)\n\nCREATE PROCEDURE getSomething @keyList varchar(4096)\nAS\nSELECT * FROM mytbl WHERE name IN (SELECT Value FROM [Global_Split] (@itemIds,','))\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/6369', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/779/']