Handle array in stored procedure

Question

User1510859543 posted

We have an asp.net web form that we want to pass the results of 1 or more selections from a ListBox to a stored procedure.  I have the code to loop through the listbox and select the values of each selected item but now we need to pass it to a stored procedure that will split the results in the WHERE clause, e.g. WHERE EmployeeCode IN(array).

Answer 1

User753101303 posted

Hi,

You have mutliple ways to do that. For example you could use https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017 (or write your own function or use an alternate approach if using an earlier version).

Another option is to use https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

You can also use XML or JSON support.

Answer 2

User632428103 posted

Hello all,

@dlChase -> in a project i have done this , pass a long string with comma separated

split all, insert the result into temporary table

and finish by a select where field in (select field from the temp table)

For to split the parameter who is pass into your store procedure, there is a split function on google you can find the code easy.