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

Siger%20Matt's gravatar image

Siger Matt
3.2k496997
accept rate: 13%


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!

permanent link

answered 06 Sep '12, 15:37

Mark%20Culp's gravatar image

Mark Culp
23.3k9132274
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
31.3k312458674
accept rate: 33%

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×19
×5
×3
×2
×1

question asked: 06 Sep '12, 15:27

question was seen: 1,049 times

last updated: 07 Sep '12, 11:02