Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

passing delimited string into stored proc

2 posts in General Discussion Last posting was on 2004-02-19 14:18:49.0Z
Ivan Posted on 2004-02-19 10:11:47.0Z
Sender: 4f3d.40348e69.1804289383@sybase.com
From: Ivan
Newsgroups: ianywhere.public.general
Subject: passing delimited string into stored proc
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40348e90.4f41.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 19 Feb 2004 02:23:12 -0800, 10.22.241.42
Lines: 85
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 19 Feb 2004 01:53:04 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 19 Feb 2004 02:11:47 -0800
X-Trace: forums-1-dub 1077185507 10.22.108.75 (19 Feb 2004 02:11:47 -0800)
X-Original-Trace: 19 Feb 2004 02:11:47 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2546
Article PK: 5684

I have been trying to resolve a technical problem but to no
avail.
Please could someone help me out?

I am passing in a delimited string into a stored procedure,
but I cannot
get
the storedproc to work.

the delimited string is passed into the storedproc as
"a,b,c,d,e" ( I have
even tried "'a','b','c','d','e'")

the stored proc is running a simple select statement:
select * from trades where broker in(@string passed in)

ALTER procedure DBA.sp_test1(@brokerstring varchar(100))

as
begin
select * from trades where broker in (@brokerstring)
end

I have tried various other methods shown below.

for example, I tried to create a temp table storing all the
brokers and
then
running a sub query

ALTER procedure DBA.sp_test2(@brokerstring varchar(100))
as
begin
declare @vsql varchar(255)
declare @broker varchar(255)
create global temporary table DBA.tbltempbrokers(
selectedbroker varchar(4) null,
) on commit delete rows
select @i=1
while(@i <= char_length(@brokerstring))
if(substring(@brokerstring,@i,1) <> ',')
begin
select @broker=@broker+substring(@brokerstring,@i,1)
insert into tbltempbrokers(selectedbroker)
values(@broker)
select @i=@i+1
end
else
begin
insert into tbltempbrokers(selectedbroker)
values(@broker)
end
select @vsql='select * from tbltempbrokers'
execute(@vsql)
drop table tbltempbrokers
end

I also tried a simple replace function - replacing all the
commas in the
delimited string with char(39) around each broker and again
seperated each
broker with a comma.

ALTER procedure DBA.sp_test1(@brokerstring varchar(100))

as
begin
declare @vsql varchar(255)
declare @broker varchar(255)
select
@broker=replace(@brokerstring,',',"char"(39)+','+"char"(39))
--select @broker = replace(@brokerstring, ',', '''' + ','
+ '''' )
select @vsql='select * from trades where broker in (' ||
"char"(39) ||
(@broker) || "char"(39) || ')'
execute(@vsql)
end

Can you please help me as I can't seem to be able to get
this working?

Many thanks in advance!

Ivan


"Bruce Hay" Posted on 2004-02-19 14:18:49.0Z
From: "Bruce Hay" <hay at sybase dot com>
Newsgroups: ianywhere.public.general
References: <40348e90.4f41.846930886@sybase.com>
Subject: Re: passing delimited string into stored proc
Lines: 164
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: hay-xp.sybase.com
X-Original-NNTP-Posting-Host: hay-xp.sybase.com
Message-ID: <4034c5c9$1@forums-1-dub>
Date: 19 Feb 2004 06:18:49 -0800
X-Trace: forums-1-dub 1077200329 172.31.142.57 (19 Feb 2004 06:18:49 -0800)
X-Original-Trace: 19 Feb 2004 06:18:49 -0800, hay-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2548
Article PK: 5686

You can solve this using the procedures below. This assumes you are using
ASA 9.0.x, which supports procedures in the FROM clause. Using ASA 9.0.x,
you could also use EXECUTE IMMEDIATE to generate the result set, but you
would need to write the procedure as a Watcom-SQL procedure (see sp_test2).
Using ASA 8.0.x, your version of sp_test2 should almost work; just eliminate
the EXECUTE at the end and return the result set by selecting from the temp
table.

ALTER procedure DBA.sp_test1(@brokerstring varchar(100))
as
begin
select * from trades
where broker in (select row_value from dbo.sa_split_list(@brokerstring))
end
go

ALTER procedure DBA.sp_test2(@brokerstring varchar(100))
begin
execute immediate with result set on
'select * from trades where broker in (' || @brokerstring || ')';
end
go


---------------------------------

call dbo.sa_make_object('procedure','sa_split_list','dbo')
go

alter procedure dbo.sa_split_list( in str long varchar, in delim char(10)
default ',', in maxlen int default 0 )
result( line_num int, row_value long varchar )
begin
declare pos int;
declare startpos int;
declare delimlen int;
declare local temporary table tresult(
line_num int primary key default autoincrement,
row_value long varchar
) in SYSTEM not transactional;
set startpos = 1;
set delimlen = length( delim );
if delimlen > 0 then
lp: loop
set pos = locate( str, delim, startpos );
if pos = 0 then
leave lp
end if;
if maxlen <> 0 and (pos - startpos) > maxlen then
set pos = startpos + maxlen;
end if;
insert into tresult( row_value )
select substr( str, startpos, pos - startpos );
set startpos = pos + delimlen;
end loop;
if length( str ) >= startpos then
insert into tresult( row_value ) values( substr( str, startpos ) );
end if;
end if;
select line_num, row_value
from tresult
order by line_num;
end
go

grant execute on dbo.sa_split_list to PUBLIC
go

--------------------------

call sp_test1('a,b,c,d,e')
go

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

<Ivan> wrote in message news:40348e90.4f41.846930886@sybase.com...
> I have been trying to resolve a technical problem but to no
> avail.
> Please could someone help me out?
>
> I am passing in a delimited string into a stored procedure,
> but I cannot
> get
> the storedproc to work.
>
> the delimited string is passed into the storedproc as
> "a,b,c,d,e" ( I have
> even tried "'a','b','c','d','e'")
>
> the stored proc is running a simple select statement:
> select * from trades where broker in(@string passed in)
>
> ALTER procedure DBA.sp_test1(@brokerstring varchar(100))
>
> as
> begin
> select * from trades where broker in (@brokerstring)
> end
>
> I have tried various other methods shown below.
>
> for example, I tried to create a temp table storing all the
> brokers and
> then
> running a sub query
>
> ALTER procedure DBA.sp_test2(@brokerstring varchar(100))
> as
> begin
> declare @vsql varchar(255)
> declare @broker varchar(255)
> create global temporary table DBA.tbltempbrokers(
> selectedbroker varchar(4) null,
> ) on commit delete rows
> select @i=1
> while(@i <= char_length(@brokerstring))
> if(substring(@brokerstring,@i,1) <> ',')
> begin
> select @broker=@broker+substring(@brokerstring,@i,1)
> insert into tbltempbrokers(selectedbroker)
> values(@broker)
> select @i=@i+1
> end
> else
> begin
> insert into tbltempbrokers(selectedbroker)
> values(@broker)
> end
> select @vsql='select * from tbltempbrokers'
> execute(@vsql)
> drop table tbltempbrokers
> end
>
> I also tried a simple replace function - replacing all the
> commas in the
> delimited string with char(39) around each broker and again
> seperated each
> broker with a comma.
>
> ALTER procedure DBA.sp_test1(@brokerstring varchar(100))
>
> as
> begin
> declare @vsql varchar(255)
> declare @broker varchar(255)
> select
> @broker=replace(@brokerstring,',',"char"(39)+','+"char"(39))
> --select @broker = replace(@brokerstring, ',', '''' + ','
> + '''' )
> select @vsql='select * from trades where broker in (' ||
> "char"(39) ||
> (@broker) || "char"(39) || ')'
> execute(@vsql)
> end
>
> Can you please help me as I can't seem to be able to get
> this working?
>
> Many thanks in advance!
>
> Ivan