Board index » delphi » Interbase, Cursor and Stored Procedure

Interbase, Cursor and Stored Procedure

Hi,

I have a simple problem (as I thought).

I have a big table and want to update a certain number of records in a
certain order.

I have to implement this in a stored proc and want to pass the number of
records as a parameter.

I can't think of a single Update-Statement to update a certain number of
records (for instance the first 100 or the first 200 - 100 and 200 should
be values of a paramter).

The idea is to implement the update as a loop:
1. Version: Declare a cursor and then iterate in a loop to the limit - I
can't declare and therefor use cursors in a stored proc. Why?

2. Version: Use For-Select-Loop and quit the loop under a condition. How
can I quit a loop For-Select-Loop before reaching the end?

I tried many things to declare a cursor - the manual says, it's possible
in SQL and DSQL. But I wasn't able to do it.

Help please!

Ingo

 

Re:Interbase, Cursor and Stored Procedure


Ingo,

I hope by this time you've solved your problem. If not, perhaps this
will help:

Stored procedures do not allow Data Definition Language (DDL) and I
believe that the declare cursor statement qualifies as DDL. I have not
tried it myself yet, so can't be sure.

You can do what you want by using the For Select...do structure if you
also use a counter in the procedure. The effect is very much like a
cursor and would look something like this:

Create procedure..........
        AS
                Declare variable acounter integer;
                Declare variable SomeData char(20);
                Declare variable KeyData integer;
        BEGIN
                acounter = 0;
                For select KeyField, FieldName from ATable
                        into :KeyData, :SomeData
                do begin
                        Somdata = 'Hello';  /* update the data */
                        Update ATable set FieldName = :SomeData
                                where KeyField = :KeyData; /* update table */
                        acounter = acounter + 1;  /* increment counter */
                        if acounter = 100 then exit; /* exit ends procedure */
                end /* end of for select */
        END /* main block */

The procedure and trigger language is not very complicated. There is
no special statement to get out of the for loop (not a very
sophisticated stack, I think).

I'll try to answer any follow up questions you may have, but you
should also know that there is newsgroup just for interbase issues and
I think you would get a quicker response on Interbase coding there.
It's news://news.mers.com.

Good luck.

Phil Cain

Quote
Ingo.Ad...@t-online.de (Ingo Adler) wrote:
>Hi,

>I have a simple problem (as I thought).

>I have a big table and want to update a certain number of records in a
>certain order.

>I have to implement this in a stored proc and want to pass the number of
>records as a parameter.

>I can't think of a single Update-Statement to update a certain number of
>records (for instance the first 100 or the first 200 - 100 and 200 should
>be values of a paramter).

>The idea is to implement the update as a loop:
>1. Version: Declare a cursor and then iterate in a loop to the limit - I
>can't declare and therefor use cursors in a stored proc. Why?

>2. Version: Use For-Select-Loop and quit the loop under a condition. How
>can I quit a loop For-Select-Loop before reaching the end?

>I tried many things to declare a cursor - the manual says, it's possible
>in SQL and DSQL. But I wasn't able to do it.

>Help please!

>Ingo

Other Threads