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 ;
asked 06 Sep '12, 15:27
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!
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 || ')';
answered 07 Sep '12, 03:50