procedure only returning 1 row
procedure only returning 1 row
(OP)
CREATE PROCEDURE usp_SubAssembly()
returns(Item_No char(15), Qty decimal(13, 6));
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;
Declare :Item char(15);
Declare :Qty decimal(13, 6);
OPEN BTUCursor;
FETCH NEXT FROM BTUCursor INTO :Item, :Qty;
select :Item, :Qty;
CLOSE BTUCursor;
end
...any thoughts? I'm new to pervasive but proficient with SQL Server
returns(Item_No char(15), Qty decimal(13, 6));
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;
Declare :Item char(15);
Declare :Qty decimal(13, 6);
OPEN BTUCursor;
FETCH NEXT FROM BTUCursor INTO :Item, :Qty;
select :Item, :Qty;
CLOSE BTUCursor;
end
...any thoughts? I'm new to pervasive but proficient with SQL Server
RE: procedure only returning 1 row
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
http://www.mirtheil.com
RE: procedure only returning 1 row
RE: procedure only returning 1 row
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.
RE: procedure only returning 1 row
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
RE: procedure only returning 1 row
The reason I say this is that the SP above can be rewritten as:
CODE
returns(Item_No char(15), Qty decimal(13, 6)) WITH DEFAULT HANDLER ;
begin
SELECT Item_No, Qty FROM MSFRCFIL_SQL
WHERE ITEM_NO Like 'M%' AND LOC = 'FG'
order by ITEM_NO asc,LOC asc;
end;
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: procedure only returning 1 row
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.