CREATE PROCEDURE DB2ADMIN.REPORT_BY_YEAR ( IN p_begin INTEGER,
                                           IN p_end INTEGER)
    SPECIFIC DB2ADMIN.REPORT_BY_YEAR
    DYNAMIC RESULT SETS  1 
------------------------------------------------------------------------
-- SQL Хранимая процедура
    -- v_begin
    -- v_end
------------------------------------------------------------------------
P1: BEGIN
    DECLARE v_current INTEGER;
    DECLARE v_dynSQL     CLOB;
    DECLARE c_too_many_columns CONDITION FOR SQLSTATE '99006';
    -- Объявить указатель
    DECLARE c_report CURSOR WITH RETURN FOR v_cur_stmt;

    SET v_dynSQL = 'select t.tname ';
    SET v_current = p_begin;

    IF  p_end - p_begin >  254  THEN
        SIGNAL c_too_many_columns SET MESSAGE_TEXT = 'Число колонок не должно превышать 255. Уменьшите диапазон по годам';
    END IF;

    WHILE (v_current <= p_end) DO
       SET  v_dynSQL = v_dynSQL || ', SUM( CASE t.tyear WHEN ' || RTRIM(CHAR(v_current)) || ' THEN t.tcnt END ) as "' || RTRIM(CHAR(v_current)) || '"';
       SET  v_current = v_current +  1 ;
    END WHILE;

    SET v_dynSQL = v_dynSQL || ' FROM( SELECT d.ID as ID, COALESCE( d.NAME, ''По всем филиалам'' ) AS TNAME  , YEAR(c.reg_date) AS TYEAR,  COUNT(c.ID ) AS TCNT ' ||
    ' FROM db2admin.districts d LEFT JOIN db2admin.customers c ON d.ID = c.district_id GROUP BY GROUPING SETS( ( d.ID, d.NAME ), () ) , c.reg_date ORDER BY COALESCE (d.ID, 0) ) t GROUP BY t.ID, t.TNAME ORDER BY t.ID';
    -- Указатель оставлен открытым для программы клиента
    PREPARE v_cur_stmt FROM v_dynSQL;
    OPEN c_report;
END P1