Field looping

Hi, is there a way to loop thru all field in a row without knowing the field name something like that
DECLARE
rec XYZ%rowtype;
BEGIN
for i in 1..10 loop
dbms_output.printline(rec(i));
end loop;
END;
? ThanksOriginally posted by nbernierpq
Hi, is there a way to loop thru all field in a row without knowing the field name something like that
DECLARE
rec XYZ%rowtype;
BEGIN
for i in 1..10 loop
dbms_output.printline(rec(i));
end loop;
END;
? Thanks
You can certainly loop through the rows without knowing the field names, but you can't output the values without using the names.

Fetch question

i am having an endless loop after the first record... anything i missed?

ALTER PROCEDURE IMPGrpEscalationX

AS

Declare @.IMID int
Declare @.IMID2 int
Declare @.FS1 int
Declare @.FS2 int

Declare crFirst cursor local for
select IMID from ImplementationGroup where IMSTatus = 'Y'
open crFirst
fetch next from crFirst
into
@.IMID

begin
Declare crSecond cursor local for
select IMID from Employees_ImplementationGroup where Employees_ImplementationGroup.IMID = @.IMID
open crSecond
fetch next from crSecond
into
@.IMID2
set @.FS2 = @.@.fetch_status
if not exists(select IMID from Employees_ImplementationGroup where IMID = @.IMID2 and IMType = 'T')
Begin
DECLARE @.MsgText varchar(700)
DECLARE @.IMGRPNAME varchar(50)
Set @.IMGRPNAME = (select IMGrpname from ImplementationGroup where IMID = @.IMID2)
--SET @.MsgText = 'This implementation group has been without a last resort implementer for the past 24 hours. Please click here to assign a last resort implementer: http://xxxx.com/admin/implementationgrp_emps.aspx?imid=' + @.IMID2 + '&imgrpname=' + @.IMGrpName

EXEC master.dbo.xp_sendmail
@.recipients = cccc@.ccc.com',
@.Message = @.IMGRPNAME,
@.Subject = 'needs attention'

end
while @.FS2 = 0
fetch next from crSecond
into
@.IMID2
end
set @.FS1 = @.@.fetch_status
while @.FS1 = 0

FETCH NEXT FROM crFirst
into
@.IMID

close crFirst
deallocate crFirst
close crSecond
deallocate crSecondi fixed this one... second fetch was outside the scope...sql

Fetch Loop stored procedure

What is wrong with this stored procedure? This should work right?

Create PROCEDURE UpdRequestRecordFwd
@.oldITIDint ,
@.newITID int
AS
Declare @.RRID int
Declare @.APID int
Declare crReqRec cursor for
select RRID from RequestRecords where ITID = @.oldITID
open crReqRec
fetch next from crReqRec
into
@.RRID
while @.@.fetch_status = 0
Begin
Update RequestRecords
set ITID = @.newITID
where RRID = @.RRID
FETCH NEXT FROM crReqRec
into
@.RRID
end
close crReqRec
deallocate crReqRec
GO

(1) what is the error you get ? its hard toguess unless you give us complete info
(2) for what you are doing above you dont seem to need a cursor.

Update RequestRecords set ITID = @.newITID where RRID = @.RRID

would work just fine without any cursor..unless you are doing other stuff in between.

either way, you need to give more details.

hth|||(1) I am not getting any error. It's just won't update
(2) I am have multiple RRID's (unique primary key) with one ITID. I want all the RRID's with this ITID to be replaced with the new ITID returned.

I removed the fetch as you suggested... still doesn't work..

Create PROCEDURE UpdRequestRecordFwd

@.oldITIDint ,
@.newITID int

AS
Declare @.RRID int

set @.RRID = (select RRID from RequestRecords where ITID = @.oldITID and RRStatus = 'IA' and APID is null)

Update RequestRecords
set ITID = @.newITID
where RRID = @.RRID

Go|||if you just need to update ITID's then how do the RRID's come into picture...
see if this helps :

Create PROCEDURE UpdRequestRecordFwd
@.oldITID int ,
@.newITID int
AS
Update RequestRecords set ITID = @.newITID where ITID = @.oldITID
Go

hth|||I do appreciate your help but it was my mistake, permission to execute was not checked. Thank you for your time.