Technical Article

Prioritised list to table

,

I wrote this to allow me to send a priority list of int ids.

This takes a "," seperated list of integers and puts them into a table, along with an [insorder] field that allows you to sort them.

eg 2,6,3,1,7 becomes

ints insorder
---- --------
1    7
2    1
3    5
6    3
7    9

Depending on what sort of query plan you need you could move the primary key to the insorder field

create function dbo.IntListStr2Tbl (
@string varchar(512),
@separator char(1) = ',' )
returns @TblResultstable(ints int primary key, insorder int)
as
begin
/*This converts a comma seperated string into a table*/declare @LenStr int, @CountAlong int, @StartLen int, @tint int

select@string = @string + @separator,@LenStr = len(@string), @CountAlong = 0

while @CountAlong < @LenStr
begin
set @StartLen = charindex(@separator, @string, @CountAlong)
if @StartLen = 0
break--safety break to prevent recursive loops

set @tint = cast(LTrim(RTrim(substring(@string, @CountAlong, (@StartLen - @CountAlong)))) as int)
if @tint is not null and @tint <> 0
insert@TblResults (ints, insorder) select @tint, @CountAlong where @tint not in (select t.ints from @TblResults as t)

set @CountAlong = @StartLen + 1
end
return 
end

Rate

Share

Share

Rate