How to use list of numbers in string as separate integers

[former_SQLA_member1694868]

I have a procedure that writes out the details of a record. I want another procedure to call it multiple times for a list of records so that the top-level procedure can end up with all of the data in one variable.

My initial structure was to call the parent procedure passing in a list of record IDs in a comma-separated string. The problem is when I try a select ... where id in ('string') I get the error "Cannot convert ... to a int"

Obviously the string is not an int, but I thought when there were only numbers in the string, it would cast it correctly. What is the correct approach for something like this?

The actual procedures and tasks are more complicated but the basic code for the procedures are below:

CREATE OR REPLACE FUNCTION tso.ExportDetails (@TDIDS varchar(240))
returns long varchar
BEGIN
declare CopySQL long varchar;

set CopySQL = ' // ====================START COPY MULTIPLE DETAIL ITEMS ========  ' ;
set CopySQL = CopySQL + ' ' ;

    FOR forDetails AS curDetails CURSOR
        FOR select DetailID from Detail
            where DetailID in (@TDIDS)
    DO
        set CopySQL = CopySQL + (select ExportSingleDetail (DetailID) into CopySQL);
        set CopySQL = CopySQL + ' ' ;

 END FOR ;

return (CopySQL);
END ;

CREATE OR REPLACE FUNCTION tso.ExportSingleDetail( @TDID int)
returns long varchar
BEGIN

declare CopySQL1 long varchar
select Detail.Data into CopySQL1 where Detail.DetailID = @TDID;
return (CopySQL1);
END ;

[MarkCulp · Accepted solution]

Replace:

where DetailID in (@TDIDS)

with

where DetailID in (select row_value from sa_split_list( @TDIDS ) )

See sa_split_list for more info.

Note that rather than using a FOR loop you could accomplish the entire thing in one query:

select list( ExportSingleDetail( row_value ), ' ' order by line_num )
  into CopySQL
  from sa_split_list( @TDIDS );
set CopySQL = ' // ====================START COPY MULTIPLE DETAIL ITEMS ========  '
           || CopySQL;

This would likely perform much better ... at least it would not be worse!

[former_SQLA_member1694868 · Accepted solution]

Excellent. Thanks Mark.

[VolkerBarth]

Just for completeness - Mark's answer is surely the way to go IMHO:

If you have a comma-separated list of integers, you could also use EXECUTE IMMEDIATE to build an according IN predicate dynamically, such as

EXECUTE IMMEDIATE 'select ... where DetailID in (' || @TDIDS || ')';

[former_SQLA_member1694868]

Yes I think something like this is how I have made it work in the past.