0

I'm working on building a procedure with two inputs, and the Syntax checks out, but the result-set is not consistent with the logic.

In this question, there are in actual fact two questions. Number 1 is the most important in this context. Number 2 is less important.


1. Inconsistency

Data Sets

    [tbl] employees (PRIM)           [tbl] loc_offices (SEC)        [tbl] emp_types (SEC)
+-----------------+-----------+  +-----------------+-----------+  +-----------+-----------+
|     COLUMNS     | DATATYPE  |  |     COLUMNS     | DATATYPE  |  |  COLUMNS  | DATATYPE  |
+-----------------+-----------+  +-----------------+-----------+  +-----------+-----------+
| emp_id          | <VARCHAR> |  | office_id       |   <INT>   |  | emp_type  | <VARCHAR> |
| emp_lastname    | <VARCHAR> |  | office_name     | <VARCHAR> |  | type_desc | <VARCHAR> |
| emp_firstname   | <VARCHAR> |  | office_town     | <VARCHAR> |  +-----------+-----------+
| emp_replocation | <VARCHAR> |  | office_province |   <INT>   |
| emp_type        | <VARCHAR> |  +-----------------+-----------+
| emp_status      |  <INT>    |
| emp_image       |  <BLOB>   |
+-----------------+-----------+

Procedure

CREATE PROCEDURE `getEmployees`(
    IN location INT(1),
    IN stafftype VARCHAR(1)
)
BEGIN
    SELECT
        emp_id,
        emp_lastname,
        emp_firstname,
        office_name,
        type_desc,
        emp_status
    FROM
        employees t1
    INNER JOIN loc_offices t2
        ON t1.emp_replocation = t2.office_id
    INNER JOIN emp_types t3 
        ON t1.emp_type = t3.emp_type
    WHERE
        IF ( 
            location = '%' AND stafftype = '%', 
            (
                t1.emp_replocation = location
                AND t1.emp_type = stafftype
            ),
            IF ( 
                location = '%' AND stafftype != '%', 
                t1.emp_type = stafftype,
                IF (
                    location != '%' AND stafftype = '%',
                    t1.emp_replocation = stafftype,
                    '%'
                )
            )
        )
    ORDER BY 
        emp_id ASC
    ;
END

Test Calls

  1. CALL getEmployees();

    • Expected Outcome: Return all records in data set

    • Outcome: "Incorrect number of arguments for PROCEDURE db.getEmployees; expected 2, got 0"

  2. CALL getEmployees(NULL, NULL);

    • Expected Outcome: Return all records in data set

    • Outcome: Empty Result-set

  3. CALL getEmployees(1, NULL);

    • Expected Outcome: Return records in data set filtered by emp_replocation only

    • Outcome: Empty Result-set

  4. CALL getEmployees(NULL, "C");

    • Expected Outcome: Return records in data set filtered by emp_type only

    • Outcome: All records, with correct emp_type

  5. CALL getEmployees(1, "C");

    • Expected Outcome: Return records in data set filtered by emp_type and emp_replocation

    • Outcome: Empty Result-set

Changes Attempted

  • Replacing (!)= '%' with IS (NOT) NULL
  • Replacing '%' with FALSE

The Question

Based on this information, why is the location parameter ignored, and what is the correction recommended in this situation?


2. The second question

I've researched this, but unsure of the correct approach. How can I handle empty Procedure Calls?

In the case of getEmployees(), it currently results in an incomplete call error, which is expected, instead of selecting all records without filters.

2
  • Data Sets Add CREATE TABLE + INSET INTO scripts. Or create a fiddle. Test Calls CALL getEmployees(); - no optional parameters in MySQL. 2nd Q. is unsolvable.
    – Akina
    May 12, 2020 at 17:10
  • How are the % replaced?
    – Rick James
    May 13, 2020 at 3:54

1 Answer 1

0
CREATE PROCEDURE `getEmployees`( IN location INT(1),
                                 IN stafftype VARCHAR(1) )
SELECT ... /* output columns list */
... /* source tables with JOIN conditions */
WHERE COALESCE(parameter, table.field) = table.field /* replace with real names */
... /* another conditions, grouping, ordering */
;

If parameter (location or stafftype) is not NULL then the condition is equal to WHERE parameter = table.field.

If parameter value is NULL then the condition is equal to WHERE table.field = table.field which is TRUE until table.field is NULL (if such records must be returned too then use <=> operator instead of =).

3
  • Could you please explain this approach? May 14, 2020 at 13:23
  • @JohanBrink Some explainations added.
    – Akina
    May 14, 2020 at 13:33
  • thank you, appreciated. May 14, 2020 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.