TSQL: Passing array/list/set to stored procedure (MS SQL Server)
Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case – it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure’s parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008).
Ok, let’s suppose that we have list of items and we need to filter this items by categories (“TV”, “TV game device”, “DVD-player”) and by firms (“Firm 1”, “Firm2”, “Firm 3). It will look at database like this
So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers – it is creating SQL-instruction with C# code, it can be like this
List<int> categories = new List<int>() { 1, 2, 3 };
StringBuilder sbSql = new StringBuilder();
sbSql.Append(
@"
select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName
from Item i
inner join Firm f on i.FirmId = f.FirmId
inner join Category c on i.CategoryId = c.CategoryId
where c.CategoryId in (");
if (categories.Count > 0)
{
for (int i = 0; i < categories.Count; i ++)
{
if (i != 0)
sbSql.Append(",");
sbSql.Append(categories[i]);
}
}
else
{
sbSql.Append("-1"); // It is for empty result when no one category selected
}
sbSql.Append(")");
string sqlQuery = sbSql.ToString();
DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=TableParameters;Integrated Security=SSPI;"))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
dataAdapter.Fill(table);
}
}
}
//TODO: Working with table
We will filter items only by categories. Just want to write less code. In the previous example first line is list of categories identifiers, chosen by user (user can select checkboxes). Problems of this solution are: a) sql-injections in some cases (user can change identifiers, which we get from web-form); b) not really good code support at feature when categories can be a really big set. One more problem – it will be hard to place this code to stored procedure (of course you can use exec statement at sql-server, but it will be not a good choice). So we can name this solution like “Solution #0”, because you can use it only if you are very lazy guy, or because this solution is very fast written.