The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
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)
        set CopySQL = CopySQL + (select ExportSingleDetail (DetailID) into CopySQL);
        set CopySQL = CopySQL + ' ' ;


return (CopySQL);

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

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

asked 06 Sep '12, 15:27

Siger%20Matt's gravatar image

Siger Matt
accept rate: 13%


where DetailID in (@TDIDS)


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!

permanent link

answered 06 Sep '12, 15:37

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

edited 06 Sep '12, 15:47

Excellent. Thanks Mark.

(07 Sep '12, 11:02) Siger Matt

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 || ')';
permanent link

answered 07 Sep '12, 03:50

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

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

(07 Sep '12, 11:02) Siger Matt
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 06 Sep '12, 15:27

question was seen: 916 times

last updated: 07 Sep '12, 11:02