ristepejov schrieb:

>
> I can't make my stored procedure use cursors,
>
> i followed the example given by Claudio Valderama in his paper about
> SQL Server vs IB, but still can't make it work.
>
> What i'm trying to do is:
>
> I want to delete several records from a table using a stored proc,
> where the sum of one of the columns in the deleted records does not
> exceed some value;
>
> Example:
>
> ID | FK | description | amount
> -----------------------------
> 1 | 14 | bla bla | 20
> 2 | 12 | bla bla | 15
> 3 | 14 | bla bla | 25
> 4 | 14 | bla bla | 10
> 5 | 14 | bla bla | 20
> 6 | 12 | bla bla | 30
> 7 | 14 | bla bla | 40
>
> So, i want to delete all the record with FK=14 but the sum(amount)
> not to be larger then 50, so i delete only ID=1 and ID=3, in that
> case the sum is 45 which is fine with the condition, and i also need
> the stored proc to return the remaining amount as return value.
>
> Can someone help, or at least advise ?

You can try sth. like:

CREATE PROCEDURE SEL_TRICKY_DEL ( MYMAX INTEGER )
RETURNS (
MYFK INTEGER,
MYREST INTEGER
)
AS
declare variable MYID INTEGER;
declare variable TMPSUM INTEGER;
declare variable MYAMOUNT INTEGER;
declare variable MYSUM INTEGER;
declare variable DO_DELETE CHAR(1);
BEGIN

FOR SELECT FK, SUM ( AMOUNT )
FROM TABLE
GROUP BY FK
INTO :MYFK, :MYSUM
DO BEGIN
/* if the total amount grouped by FK is less oyu maximum you can
delete all records with FK */
IF ( MYSUM <= MYMAX ) THEN
BEGIN
DELETE FROM TABLE WHERE FK = MYFK;
MYREST = 0;
END

ELSE BEGIN
/* otherwise you've got to select all records again and delete
them until you reached the required maximum */
/* you could also "delegate" this easily to another stored proc */
DO_DELETE = '1';
TMSPUM = 0;
MYREST = 0;

FOR SELECT ID, AMOUNT
FROM TABLE
WHERE FK = :MYAMOUNT
DO BEGIN
TMPSUM = TMPSUM + MYAMOUNT;
IF ( TMPSUM > MYMAX ) THEN DO_DELETE = '0';

IF ( DO_DELETE = '1' ) THEN
BEGIN
/* still you haven't deleted maximum */
DELETE FROM TABLE WHERE ID = :MYID;
END
ELSE MYREST = MYREST + MYAMOUNT;
END
INTO :MYID, :TMPSUM
END

/* return the REST */
SUSPEND;
END
END

Hth - and Htiwywatantmt (Hope this is what you wanted and there are not
too much typos)

Luc.