to begin, sorry for my English. I'm starting with cursors in FirebirdSQL 2.5, writing some stored procedures for a sales register system. In this case I need to calculate the credit card's taxes that are cashed by the banks in my country from the sales that the system record. Here is the SP code:
create or alter procedure CURSOR_POC
returns (
ID int,
PRICE decimal(15,2),
TAX decimal(15,2))
as
declare variable GO_ON char(1);
declare MY_CURSOR cursor for (
select CARD.CODE, sum(SALE.PRICE)
from CARD join SALE on CARD.CODE = SALE.CARD_CODE
where SALE.SALE_DATE = '14.10.2015'
group by CARD_CODE);
begin
open MY_CURSOR;
GO_ON = 'Y';
while(GO_ON='Y') do
begin
fetch MY_CURSOR into :ID, :PRICE;
if (row_count = 1) then
begin
TAX = PRICE * (select CARD.TAXES from CARD where CARD.CODE = :ID);
suspend;
end else GO_ON = 'N';
end
close MY_CURSOR;
suspend;
end
When I run the SP, its execute just fine but always show the last row twice. For instance, if I run the query in a separate file Firebird give me 10 rows, but when I run the SP I got 11 rows. Any help will be grateful. In advance, thank you very much.
P.D.: I "translate" the tables and attributes names in order to make it more understable.
P.D.2: the date filter ("dd/mm/yyyy" format) in the definition of the cursor was only define to test de stored procedure.
I will answer myself (a professor help me in a private forum from my University).
The solution was as simple as erase the last "suspend" from the stored procedure. So that, the code should be
(...previous code...)
if (row_count = 1) then
begin
TAX = PRICE * (select CARD.TAXES from CARD where CARD.CODE = :ID);
suspend;
end else GO_ON = 'N';
end
close MY_CURSOR;
end
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With