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).
Member
309 Points
1396 Posts
Handle array in stored procedure
Dec 20, 2018 02:42 PM|dlchase|LINK
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).
All-Star
43510 Points
15172 Posts
Re: Handle array in stored procedure
Dec 20, 2018 03:05 PM|PatriceSc|LINK
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.
Contributor
2183 Points
922 Posts
Re: Handle array in stored procedure
Dec 20, 2018 03:19 PM|jimmy69|LINK
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.