Sybase - Use parameter for IN clause

Question

I need a way to perform the following pattern in Sybase:

SELECT * FROM tbl WHERE x IN (@list)

The select would be a part of a stored procedure that is passed values from a web form. I would like to allow users to enter 0 to many search keys for each field, where multiple values are entered separated by some delimiter. Currently I can handle 0 to 1 keys, but not >1.

I've been trying to find an elegant solution for this similar to the pseudo SQL above, and while I have found other DBs seem to have a built in way to support (arrays for example) this it doesn't seem there is a simple answer for Sybase.

Any help would be appreciated. Please let me know if more details would help.

Solution 1

You can't really do this in SQL. The best alternative is to do string comparisons:

select *
from tbl
where ','+@list+',' like '%,'+x+',%'

Or, if you create the statement as dynamic SQL, then you can incorporate the list into the SQL query string.

Solution 2

Try this way:

declare @list varchar(100)

select @list = '1,2,3,4,5'

exec('SELECT * FROM tbl WHERE x IN ('+@list+')')

Solution 3

You can use sub select and sa_split_list system procedure to split list into separate values.

Excerpt from the documentation:

CREATE PROCEDURE ProductsWithColor( IN color_list LONG VARCHAR )
BEGIN
  SELECT Name, Description, Size, Color
  FROM Products
  WHERE Color IN ( SELECT row_value FROM sa_split_list( color_list ) )
END;
go

SELECT * from ProductsWithColor( 'white,black' );

Or use INNER JOIN instead of WHERE for filtering

  SELECT Name, Description, Size, Color
  FROM Products
  INNER JOIN sa_split_list( color_list ) FILTER ON FILTER.row_value = Products.Color