|Subject||RE: example of using first..skip in a stored procedure on Firebird 1.5?|
> On 01-Dec-2008 16:39:10, firstname.lastname@example.org wrote:Before you go too far with this, I think you might encounter the same
> 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.
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 (
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;
/* 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;
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 || '
(SELECT USER_ACCOUNT.USER_FULL_NAME FROM USER_ACCOUNT WHERE
USER_ACCOUNT.USER_ID = AUDIT_LOG.FK_USER_ID),
from AUDIT_LOG ' ||
V_WHERE_PART || V_ORDER_PART
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.
Director of Engineering
Tech Solutions USA, Inc.
Scottsdale, Arizona USA