How do you check for EOF for a cursor loop

[ABCDW]

Create Procedure testproc();
BEGIN
DECLARE ayment double;
DECLARE :Charges double;
DECLARE :trans_code varchar(9);
DECLARE :Extended double;
DECLARE c1 cursor FOR
SELECT "Billing", "Transaction Code", "Extended"
FROM "Billing Det" WHERE "Created Date" > 20010800;
OPEN c1;
FETCH NEXT FROM c1 INTO :Billing, :Trans_code, :Extended;
testloop:
LOOP
if (c1.EOF) then
LEAVE testloop;
end if;
if Trans_code > '00000' and :Trans_code < '99999') then
print 'numeric code';
else
print 'non';
end if;
FETCH NEXT FROM c1 INTO :Billing, :Trans_code, :Extended;
END LOOP;
END

When I try to run this thru SQL Data Manager, I get the following error &quot;ODBC Error: SQLSTATE = S1000, Native error code = -5099 20: 'then': Syntax error&quot;. I tried to run this after removing the C1.EOF and am nearly sure that this condition check is creating the problem? Any suggestions?

 

[Vancouverite]

EOF is not the condition to test. You must check the SQLSTATE value for '02000', e.g.:

IF (SQLSTATE = '02000') THEN
LEAVE testloop;