you can do it using cursors. somehting like this
CREATE PROCEDURE [testins] @var1 int AS
DECLARE ins CURSOR
FOR
SELECT empid,empname,address1,zipcode from
emp where rowid >@var1
OPEN ins
DECLARE @SPempid varchar(20),
@SPempname varchar(25),
@SPaddress1 varchar(25),
@SPzipcode varchar(10)
FETCH NEXT FROM ins INTO @SPempid ,@SPempname
,@SPaddress1 ,@SPzipcode
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
insert into svs (empid,empname,address1,zipcode)
values(@SPempid ,@SPempname ,@SPaddress1 ,@SPzipcode)
END
FETCH NEXT FROM ins INTO @SPempid ,@SPempname
,@SPaddress1 ,@SPzipcode
END
CLOSE ins
DEALLOCATE ins
Vani
-----Original Message-----
From: Zhou, Mei Y (Mei) [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 08, 2001 3:43 PM
To: CF-Talk
Subject: stored procedure and resultset
Can we loop all the recordsets in the stored procedures with CF 4.5 and
Oracle 8.1?
I have done this with ASP and SQL Server 7.0:
E.g.
create procedure sp_Money
select * from tbl1 where fname = "mei";
select * from tbl2 where targetSalary = "1,000,000";
then, after execute the stored procedure, we can loop it as:
objRec = NextRecordset; ( not exactly syntax, but the idea is there...)
do while not objRec.eof
--- objRec will be all the records return from the first select...;
objRec = NextRecordset;
--- this time, objRec will be all the records from the second select...
CAN we do that? Any information is greatly appreciated.
Mei
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
