Stored procedure: Searching in a table when passing an array of values

Question

Hi,

I need to create a stored procedure which receives a parameter (named @codes).

This is a string which contains a list of codes separated by a semicolumn. I'd need to look inside a table and return all rows that have a code (which is in the column EANcodes) which was passed in the @codes parameter.

Can anyone help me get started. My knowledge of stored procedures is very limited.

Thanks in advance.

Answer 1

Stored Procedures aren't really meant to handle a list of strings as a paramter. You'd be better off splitting it up in your App code and then calling the stored procedure many times with each one as a parameter.

However, if you feel the need to do it this way. You could loop through the string, and use CHARINDEX to find the next index of a semicolon and then use SUBSTRING to get the next code. Then you could use a CTE for the matching rows at each iteration and when the loop is done, simply return the CTE. This is pretty hacky, but I can't think of any other way to do this. (Those are the T-SQL string functions)

For info on the string manipulation functions (in T-SQL): http://msdn.microsoft.com/en-us/library/ms186323.aspx

And here are similar functions in MySQL: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

Answer 2

Ideally, I'd prefer to see the parameter passed in another way, either using a table-value parameter (assuming SQL 2008) or XML which can be easily shredded into a table.

Alternatively, use a SQL split function (one example is here) to parse the string into a temp table, then join against that table in your select query.