> On 01-Dec-2008 16:39:10, firebird-support@yahoogroups.com wrote:
> Due to the size of a result-set, I need to breakdown a process into a
> series of steps, but unfortunately, none of the natural data breaks are
> small enough to use.
> Thus I need to run the process using a stored procedure, controlled by
> an outside script.
>
> The script is to call the stored procedure, stating the number of rows
> to be processed and what row to start at.
>
> Unfortunately, the stored procedure will not compile, and I need an
> example of how to do this in 1.54.
>
> Here is a snippit of what I am trying to do
>
> for SELECT first :RowCount skip :StartRow a.yack from foo a into :bleah
> do begin....end
>
> The server complains on the first ':'.
>
> Any help is appreciated.

Before you go too far with this, I think you might encounter the same
problem with this that I have. The problem is that although you can use a
FIRST, SKIP type approach to the result set of the stored procedure, the
problem you have is that the stored procedure has to execute the ENTIRE
query and return the entire result set before you filter down the specific
results you want.

I had this exact same problem with a table returning about 450,000 rows and
trying to filter it down. Here's a sample of how I did it (and this was
after asking much the same question as you did to this list):

CREATE OR ALTER PROCEDURE LOG_LIST_GET_PRC (
A_ROW_COUNT INTEGER,
A_SKIP INTEGER,
A_PDTYPE VARCHAR(200),
A_SEARCH VARCHAR(200),
A_FOR VARCHAR(200),
A_ORDER_COLUMN VARCHAR(100),
A_ORDER CHAR(1))
RETURNS (
R_LOG_NO INTEGER,
R_LOG_TIMESTAMP TIMESTAMP,
R_LOG_USERID INTEGER,
R_LOG_USERNAME VARCHAR(81),
R_LOG_DESC VARCHAR(50),
R_LOG_DETAIL VARCHAR(127),
R_LOG_IPADDRESS VARCHAR(20))
AS
DECLARE VARIABLE V_SEARCH_FIELD VARCHAR(40);
DECLARE VARIABLE V_LIMIT_PART VARCHAR(200);
DECLARE VARIABLE V_WHERE_PART VARCHAR(200);
DECLARE VARIABLE V_ORDER_PART VARCHAR(200);
DECLARE VARIABLE V_SEARCH_IS_STRING CHAR(1) CHARACTER SET ISO8859_1;
begin
/* Returns a list of log entries based on critiera */
V_WHERE_PART = 'WHERE AUDIT_LOG.DA_ID > 0';
V_LIMIT_PART = '';
if (A_ORDER_COLUMN IS NOT NULL) then
V_ORDER_PART = ' ORDER BY AUDIT_LOG.' || A_ORDER_COLUMN;
ELSE
V_ORDER_PART = '';

if (A_ORDER = 'D') then
V_ORDER_PART = V_ORDER_PART || ' DESCENDING ';

/* Construct the Limit part */
if (A_ROW_COUNT IS NOT NULL) then
V_LIMIT_PART = 'FIRST ' || A_ROW_COUNT || ' SKIP ' || A_SKIP || ' ';


/* Do the search query */
for execute statement
'SELECT ' || V_LIMIT_PART || '
AUDIT_LOG.DA_ID,
AUDIT_LOG.DA_TIMESTAMP,
AUDIT_LOG.FK_USER_ID,
(SELECT USER_ACCOUNT.USER_FULL_NAME FROM USER_ACCOUNT WHERE
USER_ACCOUNT.USER_ID = AUDIT_LOG.FK_USER_ID),
AUDIT_LOG.DA_TEXT,
AUDIT_LOG.DA_LOGDETAIL,
AUDIT_LOG.DA_IPADDRESS
from AUDIT_LOG ' ||
V_WHERE_PART || V_ORDER_PART
into
:R_LOG_NO,
:R_LOG_TIMESTAMP,
:R_LOG_USERID,
:R_LOG_USERNAME,
:R_LOG_DESC,
:R_LOG_DETAIL,
:R_LOG_IPADDRESS
do
begin
suspend;
end
End
----

Change the 'WHERE PART' to whatever you need to do for the filter.

This forces the stored procedure to filter at the query level internally
rather than at the result set level, meaning a huge reduction in the time it
takes to process the query.

Hope this helps.

Myles

===============================
Myles Wakeham
Director of Engineering
Tech Solutions USA, Inc.
Scottsdale, Arizona USA
www.techsolusa.com
Phone +1-480-451-7440