Two frequently asked related questions in Microsoft SQL Server discussion groups are:
- How to pass an array to a stored procedure?
- How to select data based on a list of values?
Examples:
- User wants to search for jobs based on a list of desired locations.
- User marks e-mails in an inbox for deletion.
SQL data set based processing doesn’t support well such random-list based criteria. Sure, there is the IN operator, but how do you pass such a list to a stored procedure and how do you achieve a good execution plan without a recompilation each time the query is executed?
The Problem
Let us prepare a concrete scenario in order to be able to test and compare different solutions. Let’s define a table structure and a stored procedure that handles orders that may have different statuses during its life cycle. For example, an order may be submitted, reviewed, approved, waiting for approval, in process, rejected, closed, and so on.
Let’s define the orderStatuses table first:
create table dbo.OrderStatuses(
orderStatusID tinyInt primary key,
orderStatusName varchar(30),
orderStatusDescription varchar(255)
)
For testing purposes, we can use a simplified orders table that contains references to the user table, orderStatuses table, and additional info represented by varchar(200) column:
create table orders(
orderID int identity(1,1) primary key clustered,
userID int not null,
orderStatusID tinyInt not null,
orderInfo varchar(200)
)
In this article we will review a few implementations of the stored procedure that returns a count of orders for a given user and a list of statuses. The stored procedure structure is shown below:
create procedure orders_sel_by_userID_orderStatusList
@userId int,
@orderStatusList varchar(100)
as begin
set noCount on
<implementation code here>
end
To populate the table we can use the script below:
set noCount on
declare @StatusCount int set @StatusCount = 7
declare @UserCount int set @UserCount = 50
declare @Count int set @count = @StatusCount * @UserCount * 10000
declare @i int set @i = 1
while @i <= @count begin
insert into orders(userID, orderStatusId, orderInfo) values(@i % @UserCount + 1, @i % @StatusCount + 1, ‘IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII’)
set @i = @i + 1
if @i % 10000 = 0 print @i
end
set noCount off
Finally, we need an index on userID and orderStatusID:
create index orders_UserID_orderStatusID on orders(userID, orderStatusID)
To test performance of each implementation I used this script:
<test script> (ZIP – 3.12 KB)
I tested performance when just one statusID is passed, when two and three statuses are passed, and when different parameters are passed in subsequent executions.