procedure only returning 1 row

You need to loop through the cursor to get all of the records. The PSQL documentation is at
I'm assuming that there's more to the procedure because the example you gave doesn't even need a stored procedure. WIth PSQL, there's no performance advantage to a stored procedure.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

Yes, there is more. I have a loop inside this cursor. Boy, this is a lot easier in sql server. I need, to loop thru each item number and qty and do another loop for each one of these.

i feel stupid...

this is not working...I really need assistance.

I put this in..

OPEN BTUCursor;

FETCH NEXT FROM BTUCursor INTO :Item, :Qty;

loop

select :Item, :Qty;

FETCH NEXT FROM BTUCursor INTO :Item, :Qty;

End loop;
end;

...this query won't stop (infinite loop)

...if i take out the loop... end loop parts, i get 1 record.

i just tried this...

CREATE PROCEDURE usp_SubAssembly()

returns(Item_No char(15), Qty decimal(13, 6)) WITH DEFAULT HANDLER ;

begin


DECLARE BTUCursor CURSOR FOR
SELECT Item_No, Qty FROM MSFRCFIL_SQL
WHERE ITEM_NO Like 'M%' AND LOC = 'FG'
order by ITEM_NO asc,LOC asc FOR READ ONLY ;

Declare :Item char(15);
Declare :Qty decimal(13, 6);



OPEN BTUCursor;


FETCH NEXT FROM BTUCursor INTO :Item, :Qty;
testloop:
LOOP

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

select :Item, :Qty;

FETCH NEXT FROM BTUCursor INTO :Item, :Qty;

End loop;
CLOSE BTUCursor;
end;


...ODBC SQLSTATE S1000 Native Error Code = -1
...i am running this thru sql data manager

Perhaps we're going about this wrong. What are you trying to accomplish with this Stored Procedure? What's the end result? All of the example I see related to cursors are in conjunction with DELETE and UPDATE and INSERT statements.
The reason I say this is that the SP above can be rewritten as:

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

mirtheil,
You responded to my other post...see thread318-1273004

I really appreciate you assisting me with this. The end result is a display from crystal reports. No other program is in the mix. Basically, have the stored procedure as a source of data for the report. I wanted to write the whole thing in crystal, but that would be really slow and confusing.