Actually, you could get a couple of fairly simple solutions leveraging ranking/windowing functions and/or CTEs and recursive CTEs.
Create a procedure that accepts a character-based comma-separated list of Code values you are looking for in the sequence you want them in - use any of a dozen possible ways to split this list into a table/set that is made up of the sequence and Code value, resulting in a table with a structure like this:
declare @sequence table (sequence int not null, Code int not null);
Once you have this, it's simply a matter of sequencing the source set based on joining the sequenced table to the source table on the same Code values for a given ItemId - once you have the source set filtered and sequenced, you can simply join again based on the matching sequence values - this is sounding complex, but in reality it would be a single query like this:
with srcData as (
select row_number() over(order by t.EffectiveDate) as rn,
t.TransactionId, t.ItemId, t.Code, t.EffectiveDate, t.CreateDate
from #TableName t
join @sequence s
on t.Code = s.Code
where t.ItemId = @item_id
)
select d.TransactionId, d.ItemId, d.Code, d.EffectiveDate, d.CreateDate
from srcData d
join @sequence s
on d.rn = s.sequence
and d.Code = s.Code
order by d.rn;
This alone won't guarantee that you get a result-set that is identical to what you are looking for, but staging the data into a temp table and adding a few simple checks around the code would do the trick (for example, add a checksum validation and a sum of the code values)
declare @tempData table (rn int, TransactionId smallint, ItemId smallint, Code smallint, EffectiveDate datetime, CreateDate datetime);
with srcData as (
select row_number() over(order by t.EffectiveDate) as rn,
t.TransactionId, t.ItemId, t.Code, t.EffectiveDate, t.CreateDate
from #TableName t
join @sequence s
on t.Code = s.Code
where t.ItemId = @item_id
)
insert @tempData
(rn, TransactionId, ItemId, Code, EffectiveDate, CreateDate)
select d.rn, d.TransactionId, d.ItemId, d.Code, d.EffectiveDate, d.CreateDate
from srcData d
join @sequence s
on d.rn = s.sequence
and d.Code = s.Code;
-- Verify we have matching hash/sums
if
(
( (select sum(Code) from @sequence) = (select sum(Code) from @tempData) )
and
( (select checksum_agg(checksum(sequence, Code)) from @sequence) = (select checksum_agg(checksum(rn, Code)) from @tempData) )
)
begin;
-- Match - return the resultset
select d.TransactionId, d.ItemId, d.Code, d.EffectiveDate, d.CreateDate
from @tempData d
order by d.rn;
end;
If you want to do it all inline, you could use a different approach leveraging CTEs and recursion to perform a running sum/total and OrdPath-like comparison as well (though you'd still need to parse the sequence character data out into a dataset)
-- Sequence data with running total
with sequenceWithRunningTotal as
(
-- Anchor
select s.sequence, s.Code, s.Code as runningTotal, cast(s.Code as varchar(8000)) as pth,
sum(s.Code) over(partition by 1) as sumCode
from @sequence s
where s.sequence = 1
-- Recurse
union all
select s.sequence, s.Code, b.runningTotal + s.Code as runningTotal,
b.pth + '.' + cast(s.Code as varchar(8000)) as pth,
b.sumCode as sumCode
from @sequence s
join sequenceWithRunningTotal b
on s.sequence = b.sequence + 1
),
-- Source data with sequence value
srcData as
(
select row_number() over(order by t.EffectiveDate) as rn,
t.TransactionId, t.ItemId, t.Code, t.EffectiveDate, t.CreateDate,
sum(t.Code) over(partition by 1) as sumCode
from #TableName t
join @sequence s
on t.Code = s.Code
where t.ItemId = @item_id
),
-- Source data with running sum
sourceWithRunningSum as
(
-- Anchor
select t.rn, t.TransactionId, t.ItemId, t.Code, t.EffectiveDate, t.CreateDate,
t.Code as runningTotal, cast(t.Code as varchar(8000)) as pth,
t.sumCode
from srcData t
where t.rn = 1
-- Recurse
union all
select t.rn, t.TransactionId, t.ItemId, t.Code, t.EffectiveDate, t.CreateDate,
s.runningTotal + t.Code as runningTotal,
s.pth + '.' + cast(t.Code as varchar(8000)) as pth,
t.sumCode
from srcData t
join sourceWithRunningSum s
on t.rn = s.rn + 1
)
select d.TransactionId, d.ItemId, d.Code, d.EffectiveDate, d.CreateDate
from sourceWithRunningSum d
join sequenceWithRunningTotal s
on d.rn = s.sequence
and d.Code = s.Code
and d.runningTotal = s.runningTotal
and d.pth = s.pth
and d.sumCode = s.sumCode
order by d.rn;