CODEWITHSUNDEEP
sqlstored-proceduresparameterssybaseoptional-parameters
SilverSN95
SilverSN95

Reputation: 83

Sybase - Use parameter for IN clause

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.

Upvotes: 2

Views: 16497

Answers (5)

VitaliyG
VitaliyG

Reputation: 1857

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

Upvotes: 1

Anant Laxmikant Bobde
Anant Laxmikant Bobde

Reputation: 564

Not sure if it's too late but here is what I found when I encountered similar use case and it really helped me.

[1]: https://stackoverflow.com/questions/11101106/sybase-use-parameter-as-where-clause

Cheers

Upvotes: 0

Robert
Robert

Reputation: 25763

Try this way:

declare @list varchar(100)

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

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

Upvotes: 1

ESDictor
ESDictor

Reputation: 745

I just answered a very similar question with php and mysql, but I feel the answer applies equally here, so I'll copy/paste it.

I just handled the same problem, only in Visual Studio. First I created a string of parameters to add into the SQL statement. You only have to deal with question marks (?), so much of what I did is more than you need:

string[] inClause = new string[keywordTerms.Length];

for (int i = 0; i < keywordTerms.Length; i++)
    inClause[i] = ":keyword" + i.ToString("00");

Then when creating my select, I put the following in:

sqlStatement += "WHERE kw.keyword IN (" + String.Join(", ", inClause) + ")"

Finally, I added the parameters in this code:

for (int i = 0; i < keywordTerms.Length; i++)
    cmd.Parameters.Add(inClause[i], OracleDbType.Varchar2, 20, "keyword").Value = keywordTerms[i];

Hope that helps!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270763

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.

Upvotes: 1

Related Questions