How to use list of numbers in string as separate integers

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 Matt
3.3k●56●72●101
accept rate: 15%

flat view

2 Answers:

active answers oldest answers newest answers popular answers

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 Culp
24.9k●10●141●297
accept rate: 41%

edited 06 Sep '12, 15:47

Excellent. Thanks Mark.

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

flat view

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 Barth
40.2k●361●550●822
accept rate: 34%

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

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

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

string ×22
convert ×9
cast ×6
comma-separated ×3
integer ×1

question asked: 06 Sep '12, 15:27

question was seen: 2,996 times

last updated: 07 Sep '12, 11:02

SAP SQL Anywhere

How to use list of numbers in string as separate integers

Follow this question

Related questions