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

I hope you found a method that worked for you. The linked page is a good list of options, but I'm glad to see that most have already been suggested here! Paul suggested method 2/3, temp tables. I suggested method 1, dynamic sql. Brian and Abel suggest an XML method, although I'm not licensed for xml in sybase so don't know if that will work in Sybase. Similar to Method 4. — AdamH, Aug 21 '09 at 13:17
possible duplicate of [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) — icc97, Feb 04 '14 at 15:19

score 4 · Accepted Answer · edited Mar 23 '14 at 20:23

4

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.

edited Mar 23 '14 at 20:23

gonzobrains

7,856
14
81
132

answered Aug 14 '08 at 09:27

score 2 · Answer 2 · answered Oct 25 '11 at 16:01

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.

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.

answered Oct 25 '11 at 16:01

Ben

106
2

2

You might want to look into that... If you have Jack in the table and search for Jackie you will actually get Jack :) Also it's probably not terribly efficient, creating a string for each row in the table. – HH321 Jul 20 '12 at 09:46

Kev · Answer 3 · 2008-08-08T21:08:20.490

1

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.

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?

edited Aug 08 '08 at 21:08

answered Aug 08 '08 at 21:04

Kev

118,037
53
300
385

score 0 · Answer 4 · answered Sep 23 '08 at 20:17

0

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.

sa_split_list function

answered Sep 23 '08 at 20:17

Lurker Indeed

1,521
1
12
21

score 0 · Answer 5 · answered Sep 23 '08 at 20:36

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"'.

If you didn't want to use a temp table as in Paul's answer, then you could use dynamic sql. (Assumes 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.

score 0 · Answer 6 · edited May 29 '19 at 16:05

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

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

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

DECLARE @NameArray XML

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

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

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

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

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

score 0 · Answer 7 · answered Jun 03 '16 at 01:13

To touch on what @Abel provided, what helped me out was:

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.

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

score 0 · Answer 8 · answered Sep 07 '17 at 09:06

0

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

answered Sep 07 '17 at 09:06

user3732708

623
1
9
20

score 0 · Answer 9 · answered Sep 07 '08 at 13:18

0

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

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

answered Sep 07 '08 at 13:18

score 0 · Answer 10 · answered Aug 08 '08 at 20:57

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.

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

score 0 · Answer 11 · answered Aug 09 '08 at 00:05

0

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.

Splitting Text into Words in SQL

answered Aug 09 '08 at 00:05

Matt Hamilton

200,371
61
386
320

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

11 Answers11

Linked