Inconsistent ResultSet from Procedure with Variables 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          | &lt;VARCHAR&gt; |  | office_id       |   &lt;INT&gt;   |  | emp_type  | &lt;VARCHAR&gt; |
| emp_lastname    | &lt;VARCHAR&gt; |  | office_name     | &lt;VARCHAR&gt; |  | type_desc | &lt;VARCHAR&gt; |
| emp_firstname   | &lt;VARCHAR&gt; |  | office_town     | &lt;VARCHAR&gt; |  +-----------+-----------+
| emp_replocation | &lt;VARCHAR&gt; |  | office_province |   &lt;INT&gt;   |
| emp_type        | &lt;VARCHAR&gt; |  +-----------------+-----------+
| emp_status      |  &lt;INT&gt;    |
| emp_image       |  &lt;BLOB&gt;   |
+-----------------+-----------+


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


CALL getEmployees();

• Expected Outcome: 
Return all records in data set

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

• Expected Outcome: 
Return all records in data set

• Outcome: 
Empty Result-set
CALL getEmployees(1, NULL);

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

• Outcome: 
Empty Result-set
CALL getEmployees(NULL, "C");

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

• Outcome: 
All records, with correct emp_type
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.
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 &lt;=&gt; operator instead of =).