Re: procedure stored
I need that...
and not: where coditicu in (2,4,5)
>From: "Richard Harnden" <richard.harnden@lineone.net>
>Reply-To: "Richard Harnden" <richard.harnden@lineone.net>
>To: informix-list@iiug.org
>Subject: Re: procedure stored
>Date: Wed, 5 May 2004 12:34:48 +0100
>
>[ please don't top post ]
>
>"Araujo Guntin" <araujo_guntin@hotmail.com> wrote in message
>news:c7ag97$9g5$1@terabinaries.xmission.com...
> >Jonathan Leffler wrote in message
><pb%lc.7647$V97.1199@newsread1.news.pas.earthlink.net>
> > >Araujo wrote:
> > > > I created the following procedure stored:
> > > >
> > > > create procedure sp_selectin(incoditicu char(30000))
> > > > returning integer, char(50) ;> > > > define p_coditicu integer ;
> > > > define p_nombticu char(50);
> > > > foreach
> > > > select coditicu, nombticu
> > > > into p_coditicu, p_nombticu
> > > > from tticu
> > > > where coditicu in (incoditicu)
> > > > return p_coditicu, p_nombticu with resume ;> > > > end foreach;
> > > > end procedure;
> > > >
> > > > incoditicu -> is sequence of number that I'll use in 'where coditicu
>in
> > > > (incoditicu)' sentences.
> > > >
> > > > But, the problems is when I call to sp_selectin procedure an string
>like
> > >...
> > > >
> > > > String coditicu = "2,4,5";
> > > >
> > > > {call sp_coditicu(" + coditicu + ")}
> > > >
> > > > The procedure understand it how different arguments.
> > > >
> > > > What can I do?
> > >
> > >What you're trying to do is called Dynamic SQL, and SPL (Stored
> > >Procedure Language) does not support it. You could get yourself the
> > >Dynamic SQL datablade - assuming you have IDS 9.30 or 9.40 (well, 9.x,
> > >but you shouldn't be using an x < 30). Failing that, you can play
> > >clever tricks with default arguments.
> > >
> >
> > Thank you, but when I use:
> > > > create procedure sp_selectin(incoditicu char(30000))> > ....
> > > > where coditicu = incoditicu
> > ...
> > It's work successfully.
>
>Are you sure ?
>
>For sp_selectin("2), then it's okay;
>for sp_selectin("2,4,5"), then it isn't.
>
>Remember that:
>
>create procedure sp_selectin(incoditicu char(30000))>...
>where coditicu in (incoditicu)
>
>and, execute procedure sp_selectin("2,4,5")
>
>results in: where coditicu in ("2,4,5")
>
>and not: where coditicu in (2,4,5) -- which is what you really want.
>
> >
> > How could I know my IDS version? I use Informix SE 7.25
> >
>
>If you're using SE then you don't have an IDS version since they're
>different products.
>(you should've said you were using SE in the first place)
>
>For SE you'll have to populate a temporary table, something like:
>
>create procedure sp_selectin()>...
>where coditicu in (select coditicu from coditicu_tmp)
>
>and
>
>create temp table coditicu_tmp
>(
> coditicu integer
>) with no log;>
>insert into coditicu_tmp values (2);
>insert into coditicu_tmp values (4);
>insert into coditicu_tmp values (5);>
>execute procedure sp_selectin();>
>--
>rh
>
_________________________________________________________________
Las mejores tiendas, los precios mas bajos, entregas en todo el mundo,
YupiMSN Compras: http://latam.msn.com/compras/
sending to informix-list