Runtime error when combining working stored procs in a trigger

[Former Member]

SQLA 16.0.0.1691

I have a working Procedure return_premp_seniority_increment that returns a result set with a single row.

I have a working function return_premp_seniority that has a DO ENDFOR cursor loop, then a select the result from Procedure "A", and returns an integer.

When I execute the Procedure from within a trigger, I get an error "COMMIT/ROLLBACK not allowed within atomic operation".

Tracing with the debugger on the trigger proceeds as expected into the Procedure. But it appears when the CLOSE CURSOR executes at the end of that Procedure, I lose any debugger steps and the error appears in my ISQL client.

The trigger executes successfully when I comment out the one line which executes the Procedure.

The trigger is lengthy, so I have only included the code down to the point where the procedure call is. I have included all the code for the Procedure and Function.

Any help with what I might be doing wrong will be greatly appreciated.

Bill

-----TRIGGER SNIPPET------------------------------------------------------------

ALTER TRIGGER prreg_pay_register_bu BEFORE UPDATE OF prreg_status ON app_owner.prreg_pay_register
        REFERENCING OLD AS "OLD" NEW AS "NEW"
        FOR EACH ROW
    BEGIN
        DECLARE ls_done                                 CHAR(1);
        DECLARE ln_prset_sick_accrual_rate              NUMERIC(7, 6);
        DECLARE ln_prset_vacation_accrual_rate_basic    NUMERIC(7, 6);
        DECLARE ln_prset_vacation_accrual_rate_1        NUMERIC(7, 6);
        DECLARE ln_prset_vacation_accrual_rate_6        NUMERIC(7, 6);
        DECLARE ln_prset_vacation_accrual_rate_13       NUMERIC(7, 6);
        DECLARE ln_prset_vacation_accrual_rate_21       NUMERIC(7, 6);
        DECLARE ln_prset_vacation_accrual_rate          NUMERIC(7, 6);
        DECLARE ln_employee_vacation_accrual_rate       NUMERIC(7, 6);

        DECLARE li_debit_account                    integer;
        DECLARE li_credit_account                   integer;
        DECLARE li_syset_co_ei_expense_account      integer;
        DECLARE li_syset_co_cpp_expense_account     integer;
        DECLARE li_syset_ei_payable_account         integer;
        DECLARE li_syset_cpp_payable_account        integer;
        DECLARE li_syset_wages_payable_account      integer;
        DECLARE ls_premp_id                         varchar(6);
        DECLARE ls_lst_premp_id                     varchar(6);
        DECLARE li_prreg_id                         integer;
        DECLARE li_prldc_line_number                integer;
        DECLARE ld_prreg_pay_date                   date;
        DECLARE ld_prreg_accounting_date            date;
        DECLARE ldc_prldc_wage_payable_amount       decimal;
        DECLARE ldc_prldc_stat_co_amount            decimal;
        DECLARE ls_gldpt_department                 varchar(6);
        DECLARE ls_glprg_program                    varchar(6);
        DECLARE ls_glfun_funding_source             varchar(6);
        DECLARE ls_gldtl_detail                     varchar(6);
        DECLARE li_glact_account_cr                 integer;
        DECLARE li_glact_account_dr                 integer;
        DECLARE ls_prlin_type                       varchar(128);
        DECLARE ls_prlin_id                         varchar(10);
        DECLARE ls_prldc_description                varchar(35);
        DECLARE ls_prlin_ern_absence_type           varchar(6);
        DECLARE ldc_ern_hours                       decimal;
        DECLARE ldc_comp_hours                      decimal;
        DECLARE ldc_overtime_hours                  decimal;
        DECLARE ldc_sick_hours                      decimal;
        DECLARE ldc_holiday_hours                   decimal;
        DECLARE ldc_bereavement_hours               decimal;
        DECLARE ldc_absence_paid                    decimal;
        DECLARE ldc_absence_not_paid                decimal;
        DECLARE ldc_late_paid                       decimal;
        DECLARE ldc_late_not_paid                   decimal;
        DECLARE li_prabs_line_number                integer;
        DECLARE lui_prar_created_from_id            unsigned integer;
        DECLARE lui_prarpt_created_from_id          unsigned integer;
        DECLARE ls_prar_type                        varchar(128);
        DECLARE lui_prar_arsetu_id                  unsigned integer;
        DECLARE lui_prarpt_arsetu_id                unsigned integer;
        DECLARE lui_prar_payroll_deduction_id       unsigned integer;
        DECLARE ls_prarpt_automatically_distribute  CHAR(1);
        DECLARE li_arsetu_glact_account             INTEGER;
        DECLARE lbi_hsehld_id                       BIGINT;
        DECLARE lbi_htran_id                        BIGINT;
        DECLARE li_employee_seniority_days              INTEGER;
        DECLARE li_employee_seniority_years             INTEGER;
        DECLARE ls_accrue_holiday_on_this_entry         CHAR(1);
        DECLARE lui_housing_arsetu_id               UNSIGNED INTEGER;
        DECLARE ldc_accrue_sick_hours               DECIMAL(9, 5);
        DECLARE ldc_accrue_holiday_hours            DECIMAL(9, 5);

        DECLARE cursor_prldc INSENSITIVE CURSOR FOR
        SELECT  prldc_ledger.premp_id,
            prldc_ledger.prreg_id,
            prldc_ledger.prldc_line_number,
            prreg_pay_register.prreg_pay_date,
            prreg_pay_register.prreg_accounting_date,
            prldc_ledger.prldc_wage_payable_amount,
            prldc_ledger.prldc_stat_co_amount,
            prldc_ledger.prldc_ern_hours,
            prldc_ledger.gldpt_department,
            prldc_ledger.glprg_program,
            prldc_ledger.glfun_funding_source,
            prldc_ledger.gldtl_detail,
            prldc_ledger.glact_account_cr,
            prldc_ledger.glact_account_dr,
            Upper(prlin_line_item_setup.prlin_type),
            Upper(prlin_line_item_setup.prlin_id),
            prldc_ledger.prldc_description,
            Upper(prlin_line_item_setup.prlin_ern_absence_type),
            prar_created_from_id,
            prar_type,
            prar_emp_acct_rec.arsetu_id,
            prarpt_created_from_id,
            prarpt_emp_acct_rec_pay_terms.arsetu_id,
            prarpt_automatically_distribute,
            hsehld_id,
            (SELECT IF EXISTS(SELECT empworkauth_id FROM empworkauth_employee_work_authorization WHERE prldc_ledger.empworkauth_id = empworkauth_employee_work_authorization.empworkauth_id AND
                                    empworkauth_vacation_accrued_paid = 'accrued' AND
                                    (empworkauth_automatic_hours IS NULL OR
                                    empworkauth_automatic_hours = 0)) THEN 'Y' ELSE 'N' ENDIF) AS accrue_holiday_on_this_entry
            FROM    prldc_ledger LEFT OUTER JOIN prar_emp_acct_rec              ON prldc_ledger.prar_created_from_id = prar_emp_acct_rec.prar_id
                                 LEFT OUTER JOIN prarpt_emp_acct_rec_pay_terms  ON prldc_ledger.prarpt_created_from_id = prarpt_emp_acct_rec_pay_terms.prarpt_id,
                prlin_line_item_setup,
                prreg_pay_register 
            WHERE   prlin_line_item_setup.prlin_id = prldc_ledger.prlin_id AND
                prldc_ledger.prreg_id = prreg_pay_register.prreg_id AND
                prldc_ledger.prreg_id = "new".prreg_id
            ORDER BY    prldc_ledger.premp_id asc,
                    prldc_ledger.prldc_line_number asc
            FOR READ ONLY;

    IF "new".prreg_status = 'S' AND old.prreg_status <> 'S'     THEN
        //Get Control Codes
        SELECT  syset_co_ei_expense_account,
            syset_co_cpp_expense_account,
            syset_ei_payable_account,
            syset_cpp_payable_account,
            syset_wages_payable_account
            INTO    li_syset_co_ei_expense_account,
                li_syset_co_cpp_expense_account,
                li_syset_ei_payable_account,
                li_syset_cpp_payable_account,
                li_syset_wages_payable_account
            FROM    syset_system_setup
            WHERE   syset_id  =  0;
        IF sqlcode <> 0     THEN
            RAISERROR 18406 '18406- Prreg Trigger Failed read on syset_system_setup.syset_payables_account: ' || STRING(sqlcode)
        END IF;

        --RETRIEVE ACCRUAL RATES
        SELECT TOP 1
            prset_sick_accrual_rate,
            prset_vacation_accrual_rate_basic,
            prset_vacation_accrual_rate_1,
            prset_vacation_accrual_rate_6,
            prset_vacation_accrual_rate_13,
            prset_vacation_accrual_rate_21
            INTO    ln_prset_sick_accrual_rate,
                    ln_prset_vacation_accrual_rate_basic,
                    ln_prset_vacation_accrual_rate_1,
                    ln_prset_vacation_accrual_rate_6,
                    ln_prset_vacation_accrual_rate_13,
                    ln_prset_vacation_accrual_rate_21
            FROM    prset_setup
            ORDER BY prset_id;
        IF sqlcode < 0   THEN
            RAISERROR 18901 '18901 prreg_pay_register_bu prset SQL error ' || STRING(sqlcode);
        END IF;

        OPEN cursor_prldc;

        cursorloop1: LOOP
            FETCH NEXT cursor_prldc INTO
                ls_premp_id,
                li_prreg_id,
                li_prldc_line_number,
                ld_prreg_pay_date,
                ld_prreg_accounting_date,
                ldc_prldc_wage_payable_amount,
                ldc_prldc_stat_co_amount,
                ldc_ern_hours,
                ls_gldpt_department,
                ls_glprg_program,
                ls_glfun_funding_source,
                ls_gldtl_detail,
                li_glact_account_cr,
                li_glact_account_dr,
                ls_prlin_type,
                ls_prlin_id,
                ls_prldc_description,
                ls_prlin_ern_absence_type,
                lui_prar_created_from_id,
                ls_prar_type,
                lui_prar_arsetu_id,
                lui_prarpt_created_from_id,
                lui_prarpt_arsetu_id,
                ls_prarpt_automatically_distribute,
                lbi_hsehld_id,
                ls_accrue_holiday_on_this_entry;
            IF sqlcode = 100   THEN
                SET ls_done = 'Y';
            END IF;
            IF sqlcode < 0   THEN
                RAISERROR 18400 '18400- Prreg Trigger Failed read on prldc cursor: ' || STRING(sqlcode);
            END IF;

            IF ls_done IS NOT NULL OR ls_premp_id <> COALESCE(ls_lst_premp_id, '')   THEN
                //WRITE ACCRUAL TOTALS FOR PREVIOUS EMPLOYEE
                IF ls_lst_premp_id IS NOT NULL   THEN
                    IF ldc_accrue_sick_hours <> 0 OR ldc_accrue_holiday_hours <> 0   THEN
                        INSERT INTO prabs_employee_absence
                            (premp_id,
                            prabs_date,
                            prabs_type,
                            prabs_sick_hours,
                            prabs_holiday_hours,
                            prabs_comments)
                            VALUES
                                (ls_lst_premp_id,
                                ld_prreg_pay_date,
                                'pay-period-accrual-generated',
                                IF ldc_accrue_sick_hours    = 0 THEN NULL ELSE ROUND(ldc_accrue_sick_hours,     2) ENDIF,
                                IF ldc_accrue_holiday_hours = 0 THEN NULL ELSE ROUND(ldc_accrue_holiday_hours,  2) ENDIF,
                                'Accrued from Pay Register ' + STRING(li_prreg_id));
                        IF sqlcode <> 0   THEN
                            RAISERROR 18900 '18900 - Prreg Trigger Failed insert on prabs_employee_absence: ' || STRING(sqlcode);
                        END IF;

                    END IF;
                END IF;

                //HAVE ALL ROWS BEEN PROCESSED?
                IF ls_done IS NOT NULL   THEN
                    LEAVE cursorloop1;
                END IF;

                //ZERO ACCRUAL ACCUMULATORS
                SET ls_lst_premp_id             = ls_premp_id;
                SET ldc_accrue_sick_hours       = 0;
                SET ldc_accrue_holiday_hours    = 0;

                //DETERMINE VACATION ACCRUAL RATE FOR THIS EMPLOYEE BASED ON SENIORITY
                MESSAGE 'prreg_pay_register_bu trigger processing employee ' || ls_premp_id TO CONSOLE DEBUG ONLY;
                SELECT return_premp_seniority(ls_premp_id, ld_prreg_pay_date) INTO li_employee_seniority_days;
                SET li_employee_seniority_years = li_employee_seniority_days / 365;
                //VACATION ACCRUAL RATES ARE BASED ON WHEN YOU *START* YOUR YEAR, SO IF YOU ARE STARTING YEAR 12 YOU GET THE 13 YEAR RATE
                CASE
                    WHEN li_employee_seniority_years >= 20  THEN    SET ln_employee_vacation_accrual_rate = ln_prset_vacation_accrual_rate_21;
                    WHEN li_employee_seniority_years >= 12  THEN    SET ln_employee_vacation_accrual_rate = ln_prset_vacation_accrual_rate_13;
                    WHEN li_employee_seniority_years >= 5   THEN    SET ln_employee_vacation_accrual_rate = ln_prset_vacation_accrual_rate_6;
                    ELSE                                            SET ln_employee_vacation_accrual_rate = ln_prset_vacation_accrual_rate_1;
                END CASE;
            END IF;
code continues but is not included.....

  -----FUNCTION------------------------------------------------------------------------------------
ALTER FUNCTION app_owner.return_premp_seniority(IN as_premp_id VARCHAR(128), IN ad_as_of_date DATE)
    RETURNS INTEGER
    BEGIN
        DECLARE ld_max_prsenior_date        DATE;
        DECLARE li_total_prsenior_days      INTEGER;
        DECLARE li_days                     INTEGER;

        SET li_total_prsenior_days = 0;
        FOR prsenior AS prsenior_csr NO SCROLL CURSOR FOR
            SELECT  prsenior_action,
                    prsenior_date,
                    prsenior_days
                FROM    prsenior_employee_seniority
                WHERE   premp_id = as_premp_id AND
                        prsenior_date <= ad_as_of_date
                ORDER BY prsenior_date
        DO
            SET ld_max_prsenior_date = prsenior_date;
            IF prsenior_action = 'new-beginning-balance'   THEN
                SET li_total_prsenior_days = 0;
            END IF;
            SET li_total_prsenior_days = li_total_prsenior_days + prsenior_days;
        END FOR;

        IF ld_max_prsenior_date IS NULL OR ld_max_prsenior_date < ad_as_of_date   THEN
            SELECT an_days INTO li_days
                FROM    return_premp_seniority_increment(as_premp_id, ad_as_of_date);
            IF sqlcode < 0   THEN
                RAISERROR 18330 '18330 return_premp_seniority SQL error ' || STRING(sqlcode);
            END IF;
            SET li_total_prsenior_days = li_total_prsenior_days + li_days;
        END IF;

        RETURN li_total_prsenior_days;

    END

`

---PROCEDURE------------------------------------------------------------------
    :::SQL
    ALTER PROCEDURE app_owner.return_premp_seniority_increment(IN as_premp_id VARCHAR(128), IN ad_effective_date DATE)
RESULT( an_days             INTEGER,
        as_narrative        VARCHAR(32767))
BEGIN
    DECLARE LD_PAY_HISTORY_START_DATE       DATE;
    DECLARE LD_EMPLOYMENT_STATUS_START_DATE DATE;
    DECLARE ln_seniority_days               INTEGER;
    DECLARE ls_narrative                    VARCHAR(32767);
    DECLARE li_days                         INTEGER;
    DECLARE ld_start_date                   DATE;
    DECLARE ld_prsenior_date_starting       DATE;
    DECLARE ld_prreg_pay_date               DATE;
    DECLARE ld_prreg_pay_date_ending        DATE;
    DECLARE ld_lst_prreg_pay_date_ending    DATE;
    DECLARE ld_emptype_date_start           DATE;
    DECLARE ld_emptype_date_end             DATE;
    DECLARE ld_empstat_date_start           DATE;
    DECLARE ld_empstat_date_end             DATE;
    DECLARE ls_empstatcode_seniority_ind    CHAR(1);
    DECLARE li_employment_status_done       INTEGER;
    DECLARE li_pay_history_done             INTEGER;
    DECLARE li_paid_days                    INTEGER;
    DECLARE li_paid_days_total              INTEGER;
    DECLARE li_not_paid_days                INTEGER;
    DECLARE li_not_paid_days_total          INTEGER;

    DECLARE employment_cursor NO SCROLL CURSOR FOR
        SELECT  emptype_date_start,
                emptype_date_end,
                empstat_date_start,
                IF emptype_date_end IS NOT NULL THEN IF empstat_date_end IS NULL THEN emptype_date_end ELSE IF emptype_date_end < empstat_date_end THEN emptype_date_end  ELSE empstat_date_end ENDIF ENDIF ELSE  empstat_date_end ENDIF AS empstat_date_end,
                empstatcode_seniority_ind
            FROM    empstat_employee_status RIGHT OUTER JOIN emptype_employee_type ON empstat_employee_status.emptype_id = emptype_employee_type.emptype_id,
                    empstatcode_employee_status_code RIGHT OUTER JOIN empstat_employee_status ON empstatcode_employee_status_code.empstatcode_code = empstat_employee_status.empstatcode_code
            WHERE   premp_id = as_premp_id
            ORDER BY    emptype_date_start,
                        empstat_employee_status.emptype_id,
                        empstat_date_start,
                        empstat_id
            FOR READ ONLY;

    DECLARE pay_history_cursor NO SCROLL CURSOR FOR
        SELECT  DISTINCT    prreg_pay_date,
                            CAST(IF prcyc_id = 'M' THEN DATEADD(DAY, -1, (DATEADD(MONTH, 1, prreg_pay_date))) ELSE DATEADD(DAY, 13, prreg_pay_date) ENDIF AS DATE) AS prreg_pay_date_ending
            FROM    prldp_ledger KEY JOIN prreg_pay_register
            WHERE   premp_id = as_premp_id AND
                    prreg_pay_date_ending <= ad_effective_date
            ORDER BY    prreg_pay_date
            FOR READ ONLY;

--SET OPTION debug_messages = 'ON';

    SET LD_PAY_HISTORY_START_DATE       = DATE('1995-1-1');
    SET LD_EMPLOYMENT_STATUS_START_DATE = DATE('2010-7-24');
    SET ln_seniority_days               = 0;

    --FETCH FIRST PAY HISTORY ROW FOR POSSIBLE USE IN MULTIPLE PLACES
    OPEN pay_history_cursor;
    IF sqlcode <> 0   THEN
        RAISERROR 18310 '18310 return_premp_seniority_increment, Failed open cursor, sqlcode=' || STRING(Sqlcode);
    END IF;
    FETCH NEXT pay_history_cursor
        INTO    ld_prreg_pay_date,
                ld_prreg_pay_date_ending;
    IF sqlcode = 100   THEN
        SET li_pay_history_done = 1;
    END IF;
    IF sqlcode < 0   THEN
        RAISERROR 18315 '18315 return_premp_seniority_increment, sql error ' || STRING(sqlcode);
    END IF;

    --FETCH FIRST EMPLOYMENT HISTORY ROW FOR POSSIBLE USE IN MULTIPLE PLACES
    OPEN employment_cursor;
    IF sqlcode <> 0   THEN
        RAISERROR 18330 '18330 return_premp_seniority_increment, Failed open cursor, sqlcode=' || STRING(Sqlcode);
    END IF;
    FETCH NEXT employment_cursor
        INTO    ld_emptype_date_start,
                ld_emptype_date_end,
                ld_empstat_date_start,
                ld_empstat_date_end,
                ls_empstatcode_seniority_ind;
    IF sqlcode = 100   THEN
        SET li_employment_status_done = 1;
    END IF;
    IF sqlcode < 0   THEN
        RAISERROR 18335 '18335 return_premp_seniority_increment, sql error ' || STRING(sqlcode);
    END IF;

    --STEP 1: DETERMINE STARTING DATE FOR CALCULATIONS---------------------------------------------------------------
    --  IF THERE ARE ANY PRSENIOR ROWS, THAT DETERMINES STARTING DATE
    --  OTHERWISE PICK EARLIEST OF:
    --      a) EARLIEST PAY HISTORY DATE
    --      b) EARLIEST EMPTYPE_EMPLOYEE_TYPE START DATE
    SELECT DATEADD(DAY, 1, MAX(prsenior_date))
        INTO ld_prsenior_date_starting
        FROM prsenior_employee_seniority
        WHERE   premp_id = as_premp_id AND
                prsenior_date < ad_effective_date;
    IF sqlcode < 0   THEN
        RAISERROR 18400 '18400 return_premp_seniority_increment, sql error ' || STRING(sqlcode);
    END IF;
    IF ld_prsenior_date_starting IS NOT NULL   THEN
        SET ld_start_date = ld_prsenior_date_starting;
    ELSE
        IF ld_prreg_pay_date IS NOT NULL   THEN
            SET ld_start_date = ld_prreg_pay_date;
        ELSE
            SET ld_start_date = ld_emptype_date_start;
        END IF;
    END IF;

    SET ls_narrative = concatenate_text(ls_narrative, 'Start Date:' || COALESCE(DATEFORMAT(ld_start_date, 'dd-Mmm-yyyy'), 'Undeterminable') || '.  End Date:' + DATEFORMAT(ad_effective_date, 'dd-Mmm-yyyy'));
    IF ld_start_date IS NULL   THEN
        SET ls_narrative    = concatenate_text(ls_narrative, '>>>Undeterminable Start Date: No previous Seniority entries, no Pay History, and no Employment History<<<');
        SET ld_start_date   = ad_effective_date;
    END IF;

    --STEP 2: ACCUMULATE DAYS BETWEEN STARTING DATE AND 31 DEC 1994 (WHEN WE DONT HAVE PAY HISTORY AND HAVE TO ASSUME FULL EMPLOYMENT)--------------------------------------
    IF ld_start_date < LD_PAY_HISTORY_START_DATE   THEN
        SET li_days = DATEDIFF(DAY, ld_start_date, LD_PAY_HISTORY_START_DATE) + 1;
        SET ln_seniority_days = ln_seniority_days + li_days;
        SET ls_narrative = concatenate_text(ls_narrative, 'Accumulate days prior to 1995: ' || STRING(li_days));
    END IF;

    --STEP 3: FROM 1 JAN 1995 THRU 23 JUL 2010 (WHEN NEW EMPLOYEE STATUS WENT INTO EFFECT) ACCUMULATE DAYS BASED ON PAY HISTORY----------------------------------------------
    SET li_paid_days_total          = 0;
    SET li_not_paid_days_total      = 0;
    WHILE li_pay_history_done IS NULL   LOOP
        IF ld_prreg_pay_date >= ld_start_date AND ld_prreg_pay_date BETWEEN LD_PAY_HISTORY_START_DATE AND LD_EMPLOYMENT_STATUS_START_DATE   THEN  
            IF ld_lst_prreg_pay_date_ending IS NOT NULL   THEN
                SET li_not_paid_days        = DATEDIFF(DAY, ld_lst_prreg_pay_date_ending, ld_prreg_pay_date) - 1;
                IF li_not_paid_days > 0   THEN
                    SET li_not_paid_days_total = li_not_paid_days_total + li_not_paid_days;
                    SET ls_narrative = concatenate_text(ls_narrative, 'No Pay between ' || DATEFORMAT(ld_lst_prreg_pay_date_ending, 'dd-Mmm-yyyy') || ' and ' || DATEFORMAT(ld_prreg_pay_date, 'dd-Mmm-yyyy') || ', ' || STRING(li_not_paid_days) || ' not paid days');
                END IF;
            END IF;
            SET li_paid_days        = DATEDIFF(DAY, ld_prreg_pay_date, ld_prreg_pay_date_ending) + 1;
            SET li_paid_days_total  = li_paid_days_total + li_paid_days;
            SET ls_narrative = concatenate_text(ls_narrative, 'Pay History ' || DATEFORMAT(ld_prreg_pay_date, 'dd-Mmm-yyyy') || ' thru ' || DATEFORMAT(ld_prreg_pay_date_ending, 'dd-Mmm-yyyy') || ', ' || STRING(li_paid_days) || ' paid days');
            SET ld_lst_prreg_pay_date_ending = ld_prreg_pay_date_ending;
        END IF;
        FETCH NEXT pay_history_cursor
            INTO    ld_prreg_pay_date,
                    ld_prreg_pay_date_ending;
        IF sqlcode = 100   THEN
            SET li_pay_history_done = 1;
        END IF;
        IF sqlcode < 0   THEN
            RAISERROR 18500 '18500 return_premp_seniority_increment, sql error ' || STRING(sqlcode);
        END IF;
    END LOOP;
    SET ln_seniority_days   = ln_seniority_days + li_paid_days_total;
    SET ls_narrative        = concatenate_text(ls_narrative, 'Pay History = ' || STRING(li_paid_days_total) || ' paid days total.  Breaks in Pay History = ' || STRING(li_not_paid_days_total) || ' days total');

    --STEP 4: BEYOND 23 JUL 2010 (WHEN NEW EMPLOYEE STATUS WENT INTO EFFECT) ACCUMULATE DAYS BASED ON EMPLOYMENT STATUS----------------------------------------------
    SET li_paid_days_total          = 0;
    WHILE li_employment_status_done IS NULL   LOOP
        IF ld_empstat_date_end IS NULL   THEN       //NULL INDICATES IS STILL CONTINUING, STOP CALCULATIONS AT EFFECTIVE DATE
            SET ld_empstat_date_end = ad_effective_date;
        END IF;
        IF ld_empstat_date_end > ad_effective_date   THEN
            SET ld_empstat_date_end = ad_effective_date;
        END IF;
        IF ld_empstat_date_start <= ld_start_date   THEN
            SET ld_empstat_date_start = ld_start_date;
        END IF;
        IF ld_empstat_date_start <= LD_EMPLOYMENT_STATUS_START_DATE   THEN
            SET ld_empstat_date_start = LD_EMPLOYMENT_STATUS_START_DATE;
        END IF;

        SET li_days         = DATEDIFF(DAY, ld_empstat_date_start, ld_empstat_date_end) + 1;
        IF li_days > 0   THEN
            SET ls_narrative    = concatenate_text(ls_narrative, 'Employment Status History ' || DATEFORMAT(ld_empstat_date_start, 'dd-Mmm-yyyy') || ' thru ' || DATEFORMAT(ld_empstat_date_end, 'dd-Mmm-yyyy') || ', ' || STRING(li_days));
            IF ls_empstatcode_seniority_ind = 'Y'   THEN
                SET li_paid_days_total      = li_paid_days_total + li_days;
                SET ls_narrative            = ls_narrative || ' eligible days'
            ELSE
                SET ls_narrative            = ls_narrative || ' Ineligible days'
            END IF;
        END IF;
        FETCH NEXT employment_cursor
            INTO    ld_emptype_date_start,
                    ld_emptype_date_end,
                    ld_empstat_date_start,
                    ld_empstat_date_end,
                    ls_empstatcode_seniority_ind;
        IF sqlcode = 100   THEN
            SET li_employment_status_done = 1;
        END IF;
        IF sqlcode < 0   THEN
            RAISERROR 18600 '18600 return_premp_seniority_increment, sql error ' || STRING(sqlcode);
        END IF;
    END LOOP;
    SET ln_seniority_days = ln_seniority_days + li_paid_days_total;

    CLOSE pay_history_cursor;
    CLOSE employment_cursor;

    SET OPTION debug_messages = 'OFF';

    SELECT  ln_seniority_days, ls_narrative
        FROM DUMMY;

END

[VolkerBarth]

A very very wild guess: Is the CHAINED option set to 'OFF' here, so that server-side autocommit is in effect (meaning without further transaction control, each statement is committed individually)?

(I'm just wondering what else might raise that error message here when you do no explicit commits - and as far as I can see, there are no statements with automatic commits involved...)

[Former Member]

I just verified that Chained is still set to the default "On".

There are certainly no commits or rollbacks. That's probably why this confuses me so much. What I see happening is: the CLOSE CURSOR must cause some error or crash: the debugger immediately drops to the END statement in the proc, next drops to the END statement in the function, next drops to the END statement in the trigger, and then I get that error message.

[Former Member · Accepted solution]

Found the problem! After adding the EXCEPTION blocks and adding quite a few MESSAGE statements to confirm what code was executing, I found that removing the statement “SET OPTION debug_messages = 'OFF';” from the end of return_premp_seniority_increment cleared up the error.

I see once again I am not an SQLA guru, but I have to say this solution seems to me to be closely related to waving a dead chicken over the keyboard. But hey, any solution is a good thing!

Thanks everyone for your help.

message '----return_premp_seniority_increment ABOUT TO CLOSE CURSOR---------------------------------------------------------' TO CONSOLE;
    CLOSE pay_history_cursor;
message '----return_premp_seniority_increment pay_history_cursor IS CLOSED--------------------------------------------------' TO CONSOLE;
    CLOSE employment_cursor;
message '----return_premp_seniority_increment employment_cursor IS CLOSED---------------------------------------------------' TO CONSOLE;

    SET OPTION debug_messages = 'OFF';

message '----return_premp_seniority_increment ABOUT TO SELECT RESULT SET AT END OF PROC-------------------------------------' TO CONSOLE;
    SELECT  ln_seniority_days, ls_narrative
        FROM DUMMY;
EXCEPTION
   WHEN OTHERS THEN

[Former Member · Accepted solution]

OF COURSE! That's not a dead chicken. That's the SET OPTION doing it's own COMMIT. I feel much better with a hard fact than a mysterious solution.

[Former Member · Accepted solution]

Inside of a procedure... does using the TEMPORARY keyword work here? It won't do a COMMIT and it won't affect all other connections then. I'm not familiar with TSQL syntax though so that keyword may not be available to you.

[VolkerBarth · Accepted solution]

Note, Bill is not using T-SQL (of course...).

Yes, SET TEMPORARY OPTION... should work, and the docs tell:

Side effects

If TEMPORARY is not specified, an automatic commit is performed.

I would suggest a doc page with all those statements that do an automatic commit. Everyone will expect the typical DDL statements here, but SET OPTION...?

[Former Member · Accepted solution]

That SET OPTION was just a standard statement I put at the end of all my stored procs so that I don't inadvertently leave debugging on after a round of testing. Has been a "good idea" up until now 🙂

[Breck_Carter · Accepted solution]

Sorry for not seeing the SET OPTION earlier... note that SET TEMPORARY OPTION should always be the first choice for embedding with other logic since it affects only the current connection. SET OPTION changes the initial setting for all future connections, generally NOT what you want. I suggest you scan all your procs for "SET OPTION" and get rid of them 🙂

[VolkerBarth · Accepted solution]

Somewhat funny that the "SET OPTION debug_messages = 'OFF';" statement has made you debug all that code:)

[Former Member · Accepted solution]

I remember, after the TEMPORARY debug option didn't help me when my code involved firing an event (which created a separate connection), having the bright idea that the permanent option would be bulletproof. I'll add that to the list of bright ideas I have had in my career that came back to bite me in the butt :).

[Breck_Carter]

RAISERROR alone does not a robust error-handling mechanism make... intermediate error conditions may be silently lost on the way back up the food chain.

Highly recommended is an EXCEPTION handler in each and every BEGIN block that may have issues, and that handler should contain some reliable mechanism for reporting the error. Here is a template for the condition where you don't actually handle any errors, just report them all...

BEGIN

DECLARE @sqlcode   INTEGER;
DECLARE @sqlstate  VARCHAR ( 5 );
DECLARE @errormsg  VARCHAR ( 32767 );

"some code"

EXCEPTION
   WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
      MESSAGE STRING ( 
         'DIAG ', 
         CURRENT TIMESTAMP, 
         ' SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;

END;

[VolkerBarth]

...meaning you don't necessarily have to use the debugger to find issues... - just look in the console log:)

Often enough, I have not taken the time to code such generic handlers, and that saved time and more was spent later...

Just to add a FAQ with a similar template from Mark - and his explanation that this does not lead to a performance penalty:

How do I notice errors in a function used to calculate a computed column?

[Former Member]

That makes sense, I'll modify the code and see what that tells me.

I have always had what I considered really good success with raiserror and sometimes change the continue_after_raiserror option temporarily to make problems more evident (after you helped me with a mysterious error several years ago). But that didn't do anything different for me this time.

off to try exception block.

[MCMartin]

You can try if explicitly defining the cursor with the INSENSITIVE keyword as a read-only cursor changes this behavior

[Former Member]

Thanks Martin. I have just tried changing the cursors in the trigger and both procs from NO SCROLL to INSENSITIVE. But I am getting the same error.