Solution Based on a Table Containing Lists of Values and Its Members
If we could avoid the list of value parsing and still have a table with all statuses requested, we would probably have the solution we are looking for. Having a table with pre-populated lists and its members would ensure the parsing step is not needed.
create table dbo.orderStatusIDListMembers(
list varchar(50),
OrderStatusID int not null,
primary key clustered (list, orderStatusID)
)
Sample data contained in the table would be:
List |
OrderStatusID |
‘1’ |
1 |
‘1,2’ |
1 |
‘1,2’ |
2 |
… |
… |
To be able to implement this solution we have to make sure that orderStatusIDListMembers table data is in synch with the orderStatuses table. To achieve that, we need to implement procedures for inserting and deleting statuses.
create procedure dbo.OrderStatuses_ins
@orderStatusID tinyInt,
@orderStatusName varchar(30),
@orderStatusDescription varchar(255)
as begin
set noCount on
begin transaction OrderStatuses_ins
insert into dbo.OrderStatuses values(@orderStatusID, @orderStatusName, @orderStatusDescription)
if @@error <> 0 begin
rollback transaction OrderStatuses_ins
return (-1)
end
declare oldOrderStatusIDList insensitive cursor for
select distinct list
from dbo.orderStatusIDListMembers
declare @currentList varchar(50),
@currentOrderStatusID varchar(50),
@newList varchar(50)
open oldOrderStatusIDList
set @newList = null
while (0 = 0) begin
fetch next from oldOrderStatusIDList into @currentList
if @@fetch_status <> 0 break
select @newList = isNull(@newList + ‘, ‘, ”) + cast(orderStatusID as varchar(50))
from (
select orderStatusID
from dbo.orderStatusIDListMembers
where list = @currentList
union
select @orderStatusID as orderStatusID
) as t
order by orderStatusID
insert into dbo.orderStatusIDListMembers(list, orderStatusID)
select @newList, orderStatusID
from (
select orderStatusID
from dbo.orderStatusIDListMembers
where list = @currentList
union
select @orderStatusID as orderStatusID
) as t
if @@error <> 0 begin
rollback transaction OrderStatuses_ins
close oldOrderStatusIDList
deallocate oldOrderStatusIDList
return(-1)
end
set @newList = null
end
close oldOrderStatusIDList
deallocate oldOrderStatusIDList
insert into dbo.orderStatusIDListMembers(list, orderStatusID) values(cast(@orderStatusID as varchar(50)), @orderStatusID)
if @@error <> 0 begin
rollback transaction OrderStatuses_ins
return(-1)
end
commit transaction OrderStatuses_ins
return 0
end
I’ll omit the code of the procedure that deletes single status because it is not needed for performance testing and its implementation is straightforward.
To test the solution, let’s populate the orderStatuses and orderStatusIDListMembers tables.
exec dbo.OrderStatuses_ins @orderStatusID = 0, @orderStatusName = ‘0’, @orderStatusDescription = ‘0’
exec dbo.OrderStatuses_ins @orderStatusID = 1, @orderStatusName = ‘1’, @orderStatusDescription = ‘1’
exec dbo.OrderStatuses_ins @orderStatusID = 2, @orderStatusName = ‘2’, @orderStatusDescription = ‘2’
exec dbo.OrderStatuses_ins @orderStatusID = 3, @orderStatusName = ‘3’, @orderStatusDescription = ‘3’
exec dbo.OrderStatuses_ins @orderStatusID = 4, @orderStatusName = ‘4’, @orderStatusDescription = ‘4’
exec dbo.OrderStatuses_ins @orderStatusID = 5, @orderStatusName = ‘5’, @orderStatusDescription = ‘5’
exec dbo.OrderStatuses_ins @orderStatusID = 6, @orderStatusName = ‘6’, @orderStatusDescription = ‘6’
exec dbo.OrderStatuses_ins @orderStatusID = 7, @orderStatusName = ‘7’, @orderStatusDescription = ‘7’
The stored procedure implementation is similar to the previous one:
create procedure orders_sel_by_userID_orderStatusList
@userId int,
@orderStatusList varchar(100)
as begin
set noCount on
Select count(*)
from orders o
join dbo.orderStatusIDListMembers l on o.orderStatusID = l.OrderStatusID and l.list = @orderStatusList
where o.userID = @userID
end