TSQL: Passing array/list/set to stored procedure (MS SQL Server)
- modified:
- reading: 9 minutes
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
And I will show you sample of this interface
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
``` ListWe 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.
Solution #1. String – parameters separated by comma.
In all next solutions we will use stored procedures at sql server. So first solution will be a list of parameters separated by comma in one string sql-parameter, like this ‘1,2,3,4’. First we need to create function at sql server, which create a table from this string parameter, name of this function will be Split:
``` if object_id('Split') is not null drop function split go create function dbo.Split ( @String varchar(max) ) returns @SplittedValues table ( Id varchar(50) primary key ) as begin declare @SplitLength int, @Delimiter varchar(5) set @Delimiter = ',' while len(@String) > 0 begin select @SplitLength = (case charindex(@Delimiter,@String) when 0 then len(@String) else charindex(@Delimiter,@String) -1 end) insert into @SplittedValues select substring(@String,1,@SplitLength) select @String = (case (len(@String) - @SplitLength) when 0 then '' else right(@String, len(@String) - @SplitLength - 1) end) end return end ```Now we can use this function in our stored procedure for looking items:
``` if object_id('FindItems') is not null drop proc FindItems go set ansi_nulls on go set quoted_identifier on go create proc FindItems ( @categories varchar(max) ) as begin 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 inner join dbo.Split(@categories) cf on c.CategoryId = cf.Id end ```At last C# code, which will put filter to stored procedure and get list of products:
``` ListProblems of this solution can be: composite primary keys, or string identifiers with commas. But in simple cases it can works.
These problems you can solve with another solution “Passing lists to SQL Server with XML Parameters”, I don’t consider this solution in my article, because you can get a more great explanation of how to do it, because I never use this solution in my developer’s life.
Solution #2. BULK INSERT
Problems which we had at previous solution we can solve with Bulk Insert solution. We will have a code which will copy .Net DataTable object to SQL Server temporary table. First let rewrite our stored procedure FindItems:
``` if object_id('FindItems') is not null drop proc FindItems go set ansi_nulls on go set quoted_identifier on go create proc FindItems as begin if object_id('tempdb..#FilterCategory') is null begin raiserror('#FilterCategory(id int) should be created', 16, 1) return end 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 inner join #FilterCategory cf on c.CategoryId = cf.Id end ```Now our procedure expects temporary table #FilterCategory, which you should create before using this SP. And now we should write more C# code than at previous time, we will create new repository class ItemsRepository:
``` public class ItemsRepository { public static DataTable FindItems(ListMethod FindItems create new object of type DataTable, copy to this object list of identifiers (chosen by user), then method open new transaction, create on SQL server new temporary table #FilterCategories, copy DataTable to server in this temporary table and then call stored procedure FindItems. This temporary table will be live only in transaction scope, so when we will commit or rollback transaction this table will be dropped too, and if two user in same time will execute this code, each of them will have separate temporary table #FilterCategories.
I used this solution very often when I had a task – copy data from Excel file to SQL server or some other import data task.
Let find minuses of this solution: a lot of code – is not a problem, because we can refactor this code and put it in some application framework. Next minus is more than one query for this task. Other – if this stored procedure will execute some other, which will create new temporary table with same name we will have a problems (it can be some recursive call). Other minus – will be hard to work with this approach from Management Studio, we need always write a script for creating temporary table (and the main problem you need to remember which structure this table has):
``` create table #FilterCategory(id int) insert into #FilterCategory ( id ) values ( 1 ) insert into #FilterCategory ( id ) values ( 2 ) insert into #FilterCategory ( id ) values ( 3 ) insert into #FilterCategory ( id ) values ( 4 ) exec FindItems drop table #FilterCategory ```Solution #3. Table-Valued Parameters (Database Engine)
And the last solution which I want to show you – is the table-valued parameters. This approach is very similar to previous, but more simple. You can use it in MS SQL server with version 2008 or bigger. Ok, we need again rewrite out stored procedure FindItems, and we should create new table-valued type Identifiers:
``` if object_id('FindItems') is not null drop proc FindItems go if exists(select * from sys.types where name = 'Identifiers') drop type Identifiers go create type Identifiers AS TABLE ( id int primary key); go set ansi_nulls on go set quoted_identifier on go create proc FindItems ( @categories Identifiers readonly ) as begin 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 inner join @categories cf on c.CategoryId = cf.Id end go ```Also we need to rewrite C# code
``` ListNow it is very easy, and it is easier to work with this stored procedure from Management Studio:
``` declare @categories Identifiers insert into @categories ( id ) values ( 1 ) insert into @categories ( id ) values ( 2 ) insert into @categories ( id ) values ( 3 ) insert into @categories ( id ) values ( 4 ) exec FindItems @categories ```Table-valued parameters solution has some limitations, like this parameter should be always read-only. If you want to know about performance of this solution you can look at this article Table-Valued Parameters (Database Engine), also this article will show you when will be better to use Bulk Insert and when Table-valued parameters.