Answer

You can store result of one query in a variable and then use it in the next stage of stored procedure. The following example shows how you can store the result of the query in the QRY_STRING variable and then retrieve the value from the query result.

CREATE OR REPLACE PROCEDURE MY_PROC
                                    (
                                            VARCHAR(ANY)
                                    )
        RETURNS INT LANGUAGE NZPLSQL
AS
        BEGIN_PROC
        DECLARE
                --DECLARING RECORDS, VARIABLES.
                TBL_NAME ALIAS FOR $1;
                REC RECORD;
                QRY_STRING VARCHAR(200);
        BEGIN
                QRY_STRING := 'SELECT COUNT(*) AS COUNT FROM '
                || TBL_NAME;
                FOR REC IN EXECUTE QRY_STRING
                LOOP
                        RAISE NOTICE 'Count: %', REC.COUNT;
                END LOOP;
                RETURN REC.COUNT;
                --ERROR HANDLER
        EXCEPTION
        WHEN OTHERS THEN
                RAISE NOTICE 'ERROR: % !', SQLERRM;
                RETURN -1;
        END;
        END_PROC;

The following is sample output for the stored procedure: