Select Based on List of Random Values — Dynamic SQL or CSV Parsing UDF?

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:

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