Merging multiple related firebird select procedure using If else or case method

Question

How to merge this two firebird select procedure using this REFERENCE variable thru if else, case, or other method. If REFERENCE = 1 then the procedure 1 will display, if REFERENCE = 2 then the procedure 2 will display. I am trying to have 1 select procedure with conditions rather than 2 procedure.

CREATE PROCEDURE PRINT_NON_REF1(
      M VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
      Y VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
      REFERENCE VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)
    RETURNS(
      AP_PSTIONLVL_NON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
      AP_POSTION_NON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
      RANKING_MONTH VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
      RANKING_YEAR VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)
    AS
    BEGIN
      FOR
        SELECT
          '',
          '',
          RANKING_MONTH,
          RANKING_YEAR

        FROM APPLICANT
        WHERE RANKING_MONTH = :M AND RANKING_YEAR = :Y

        GROUP BY
        RANKING_MONTH,
        RANKING_YEAR

        INTO
          :AP_PSTIONLVL_NON,
          :AP_POSTION_NON,
          :RANKING_MONTH,
          :RANKING_YEAR
      DO
        BEGIN
          SUSPEND;
        END

    END;

and

CREATE PROCEDURE PRINT_NON_REF2(
      M VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
      Y VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
      REFERENCE VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)
    RETURNS(
      AP_PSTIONLVL_NON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
      AP_POSTION_NON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
      RANKING_MONTH VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
      RANKING_YEAR VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)
    AS
    BEGIN
      FOR
        SELECT
          AP_PSTIONLVL_NON,
          AP_POSTION_NON,
          RANKING_MONTH,
          RANKING_YEAR

        FROM APPLICANT
        WHERE RANKING_MONTH = :M AND RANKING_YEAR = :Y

        GROUP BY
        AP_PSTIONLVL_NON,
        AP_POSTION_NON,
        RANKING_MONTH,
        RANKING_YEAR

        INTO
          :AP_PSTIONLVL_NON,
          :AP_POSTION_NON,
          :RANKING_MONTH,
          :RANKING_YEAR
      DO
        BEGIN
          SUSPEND;
        END

    END;

Why would you even do such SPs ? They can only degrade performance while adding no value. Why not issuing `SELECT`s directly ? In general you may toy with a construct like `select-1 UNION ALL select-2` with `WHERE` clauses in those `SELECT`s checking the flag parameter. — Arioch 'The, May 06 '20 at 07:29

Arioch 'The · Accepted Answer · 2020-05-06T07:51:17.887

You may try a construct like this:

WITH
  Q_2 as (
      SELECT
        AP_PSTIONLVL_NON,
        AP_POSTION_NON,
        RANKING_MONTH,
        RANKING_YEAR
      FROM APPLICANT
      WHERE RANKING_MONTH = :M
        AND RANKING_YEAR = :Y
      GROUP BY
        AP_PSTIONLVL_NON,
        AP_POSTION_NON,
        RANKING_MONTH,
        RANKING_YEAR
  ), 
  Q_1 as (
     SELECT
       '',
       '',
       RANKING_MONTH,
       RANKING_YEAR
     FROM APPLICANT
     WHERE RANKING_MONTH = :M 
       AND RANKING_YEAR = :Y
     GROUP BY
       RANKING_MONTH,
       RANKING_YEAR
 )

SELECT * FROM Q_2 WHERE :REFERENCE=2
   UNION ALL
SELECT * FROM Q_1 WHERE :REFERENCE=1

Things to notice:

Making a stored procedure which contains nothing but a single SELECT is bad idea in Firebird/Interbase. It will disable SQL optimizer job on joining, and will add nothing of value. Firebird is not MS SQL.
The second query was set first in the unioned chain, because it is the first query where UNION takes column names and data types from. Would Q_1 go first - the two anonymous columns would be a problem.
Some database access libraries might have problems with several parameters in a query having same names. You might need to rename :M into :M_1 and :M_2, etc. Or maybe not, test your library.
Your specific programming language/library can have different SQL parameters syntax than :name used above. Especially, that Firebird itself on SQL level only supports unnamed parameters. Check your library documentation. You might also get insights reading code examples at https://bobby-tables.com/
Read documentation about UNION and CTE (Common Table Expressions) at https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html

Merging multiple related firebird select procedure using If else or case method

1 Answers1