Hi
You can use

FOR SELECT ... INTO ... DO BEGIN
    ...statements or another FOR SELECT INTO
END

in stored procedures with input and output parameters to process rows as in VFP SCAN/ENDSCAN and in the same time return rows to client.

You can even dynamically construct those SELECT statements based on table names passed as parameters and then use

FOR EXECUTE STATEMENT {cDynamicSQL} INTO ... DO BEGIN
    ...statements or another FOR SELECT INTO
END

Also you can JOIN result sets from a stored procedure with regular table/views


>Hi Dorin
>
>>Try this way
>
>Wow, Dorin. At first I thought I was looking at my original SQL refomatted, but then I noticed that CASE and ELSE was sparesly used and it worked.
>
>Can these blocks be put in Stored Procedure in FireBird? If so can the return value straight be used as the cursor field/column. A call something like
>
>SELECT MyStoredProcDr(s.bDrAmt) AS bBalDr, MyStoredProcCr(s.bCrAmt) AS bBalCr
>
>MyStoredProcDr
>

>  CASE WHEN (s.bDrAmt > 0 AND uf.bCr > 0) THEN s.bDrAmt - uf.bCr
>>       WHEN (s.bDrAmt > 0 AND uf.bDr > 0) THEN s.bDrAmt - uf.bDr
>>       WHEN (uf.bDr IS NULL AND uf.bCr IS NULL) THEN s.bDrAmt
>>       ELSE 0 END AS bBalDr,

>
>MyStoredProcCr
>

>  CASE WHEN (s.bCrAmt > 0 AND uf.bDr > 0) THEN s.bCrAmt - uf.bDr
>>       WHEN (s.bCrAmt > 0 AND uf.bCr > 0) THEN s.bCrAmt - uf.bCr
>>       WHEN (uf.bDr IS NULL AND uf.bCr IS NULL) THEN s.bCrAmt
>>       ELSE 0 END AS bBalCr

>
>
>This is just one table now. As soon as I add more tables they will be also be queried and having similar query results, irrespective of the fields the future table contains, the logic and cursor fields will remain the same as for this AccountsJV tables
>
>These cursors then I am planning to club together and then provided to the user for further adjusting entries. I guess I haven't mentioned in the thread but this is for Bill-wise (more like entry-wise) outstanding adjustments module.
>
>Thanks a lot for your help.