List to Table Conversion Function - AppGeo/GPV GitHub Wiki
Some of the stored procedures called by the GPV take in a text string containing a comma-delimited list of IDs. This text string should be converted to a table for use in the stored procedure. Below is an example list conversion function for SQL Server and Oracle that returns a table of character data. Depending on your stored procedure, you may want to create other versions of this function to return tables of other data types, such as numbers or dates.
See Query Stored Procedure, Mailing Label Procedure and Export Stored Procedure for examples of how to use this function in stored procedures.
SQL Server
create function ToCharTable(@list ntext, @delimiter nchar(1) = N',')
returns @tbl table (listpos int identity(1, 1) not null, value nvarchar(2000)) AS
begin
declare @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
set @textpos = 1
set @leftover = ''
while @textpos <= datalength(@list) / 2
begin
set @chunklen = 4000 - datalength(@leftover) / 2
set @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
set @textpos = @textpos + @chunklen
set @pos = charindex(@delimiter, @tmpstr)
while @pos > 0
begin
set @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
insert @tbl (value) values(@tmpval)
set @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
set @pos = charindex(@delimiter, @tmpstr)
end
set @leftover = @tmpstr
end
insert @tbl (value) values (ltrim(rtrim(@leftover)))
return
end
Oracle
create type CharTable as table of varchar2(50);
create or replace function ToCharTable(p_string in varchar2) return CharTable as
l_string long default p_string || ',';
l_data CharTable := CharTable();
n number;
begin
loop
exit when l_string is null;
n := instr(l_string, ',');
l_data.extend;
l_data(l_data.count) := ltrim(rtrim(substr(l_string, 1, n - 1)));
l_string := substr(l_string, n + 1);
end loop;<nowiki>
return l_data;
end ToCharTable;