@@FETCH_STATUS

Dec 2, 1999

Would anyone know how to reset @@FETCH_STATUS back to 0 after its value is -1? Thanks

Aug 9, 2007

Sorry if this is a dumb question, but I am new to SQL Server and trying a simple cursor. Works fine the first time I execute, but then @@Fetch_Status stays at -1 and it will not work again. If I disconnect and then connect it will work once again. What I am doing wrong?

declare @entityid int, @datafrom nvarchar (255),

@datato nvarchar (255), @updatedate datetime

declare statustime1 cursor

local scroll static

for

select audit.entityid, audit.datafrom, audit.datato, audit.updatedate

from audit

, orders

where audit.entityid = orders.orderid

and audit.entitytypeid = 4

order by orders.orderid, audit.updatedate desc

open statustime1

While @@fetch_status = 0

Begin

fetch next from statustime1

into @entityid, @datafrom, @datato, @updatedate

print @entityid

end

close statustime1

deallocate statustime1

go

View 13 Replies

View Related

Fetch_status/While Loop Problem

Sep 12, 2003

I'm trying to populate two tables with information retrieved from one select statement.

My problem is that the code that I have attached doesn't loop through the data. It seems to run on through only one record.

I have to admit that this is my first time really screwing around with cursors and loops, so I may be missing something really obvious.

Thanks for any help!

Declare CignaFile Cursor
For
Select
EepSSN,
Left(EepEEID, 11),
EepAddressLine1,
EepAddressLine2,
EepAddressCity,
EepAddressState,
EepAddressZipCode,
EepAddressCounty,
EepAddressCountry,
EecPhoneBusinessNumber

FROM
EmpPers left outer join EmpComp
on EepEEID = EecEEID

WHERE
EecDateOfTermination is null
and EecCOID = 'JFNBV'

Declare
@EepSSNvarchar(11),
@EepEEIDvarchar(11),
@Address1varchar(35),
@Address2varchar(35),
@Cityvarchar(30),
@Statevarchar(2),
@Zipvarchar(9),
@Countyvarchar(30),
@Countryvarchar(3),
@PhoneBusinessNumbervarchar(12),
@DateEffective varchar(10),
@Counternumeric,
@RowCountnumeric

select @Rowcount = 5
select @Counter = 0
select @DateEffective = '2003-01-01'

Open CignaFile
Fetch Next from CignaFile

Into
@EepSSN,
@EepEEID,
@Address1,
@Address2,
@City,
@State,
@Zip,
@County,
@Country,
@PhoneBusinessNumber

While @@fetch_status <> -1
Begin

select @Country =
(select case when @country is not null then @country
else ' ' end)

-----Cigna_Employee_E2

INSERT INTO Cigna_Employee_E2
(
E2SSN,
E2EEID,
E2Address1,
E2Address2,
E2City,
E2State,
E2Zip,
E2County,
E2Country,
E2DateEffective
)
Values
(
@EepSSN,
@EepEEID,
@Address1,
@Address2,
@City,
@State,
@Zip,
@County,
@Country,
@DateEffective
)

-----Cigna_Employee_E3

INSERT INTO Cigna_Employee_E3
(
E3SSN,
E3EEID,
E3PhoneNumber
)

Select
@EepSSN,
@EepEEID,
@PhoneBusinessNumber

End

Begin
Fetch next from CignaFile
Into
@EepSSN,
@EepEEID,
@Address1,
@Address2,
@City,
@State,
@Zip,
@County,
@Country,
@PhoneBusinessNumber
END

-- Last record has been processed
Close CignaFile
Deallocate CignaFile

View 3 Replies

View Related

@@FETCH_STATUS

@@FETCH_STATUS Reset

Fetch_status/While Loop Problem