RE: Stored Prcedure

["Himes, Jay" <jehimes@xxxxxxxxxxx>]

I am not completely sure what you are trying to accomplish; however,
accessing the result set returned by a user defined function is easy (see
the example below). I have not be able to access a result set returned by a
stored procedure in this manner. If you could use a UDF instead of the
stored procedure this might work for you.


Here is the function:
CREATE FUNCTION JEHIMES/TSTPRC4 (ID int)                                  
RETURNS table( id int, cod char(4) )                                      
LANGUAGE SQL READS SQL DATA                                               
NO EXTERNAL ACTION                                                        
DETERMINISTIC                                                             
DISALLOW PARALLEL

 RETURN                                                                   
   select c1nmid, c2acod from nammsp join adrmsp on c1nmid=c2nmid         
     where c1nmid=TSTPRC4.ID     
--************************************************************************
The statement to access the result set:

select * from                      
 table( jehimes/tstprc4(1) ) as tbl1



Jay Himes
Liberty University





-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
SRamanujan@xxxxxxxxxxxxxxxxxx
Sent: Thursday, March 18, 2004 11:44 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Stored Prcedure

Hi All,

I was wondering if I could declare a cursor to retrieve a result set from a
stored procedure. I have a couple of parameters that I declared as a out
(not inout) and I have problems while calling the procedure.  I called the
procedure without the out parms and it complained about mis-match parameters
then I tried to add the out parms and indicators but still no luck.  I have
a result set returned by the proc. Should I be declaring that as well with
indicator?  I am confused. Any help is welcome.

Thanks in advance,

Sudha 

 

Sudha Ramanujan

SunGard Futures Systems

sramanujan@xxxxxxxxxxxxxxxxxx <mailto:sramanujan@xxxxxxxxxxxxxxxxxx> 

(312) 577 6179

(312) 577 6101 - Fax

 

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.