Mysql – Inconsistent ResultSet from Procedure with Variables

empty stringMySQLstored-procedureswhere

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

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.

Best Answer

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 =).

Related Solutions

MySQL Stored Procedure – Loop Through Table and Delete Rows

There are two possible solutions:

set @z to null in the very beginning of your loop (prior to SET @sql_text2 = concat('....)

or instead of

SELECT id INTO @z FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1;

use this:

SET @z = (SELECT id FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1);

Explanation:

When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.
see: https://dba.stackexchange.com/a/35207/12923

Mysql – I need help optimizing a not that complicated query that returns unexpedcted performance problems

My bet is on creating the following indexes:

(incoming, targetNumber, dateCreated)
(sourceNumber, incoming, dateCreated)

But you have to rewrite your query to do:

select *
from tesis.call oc --outgoing calls
join tesis.call ic on 
    oc.sourceNumber = ic.targetNumber 
    and ic.dateCreated > oc.dateCreated 
    and (ic.dateCreated - interval 30 second) <= oc.dateCreated --incoming calls
where oc.incoming = 0 
    and ic.incoming = 1 
    and oc.neighborhood_id is not null

This seems better than doing the JOIN in this direction oc -> ic, where the indexes would be: (incoming) (targetNumber, dateCreated)

Please note that this is long shot, as I do not know the cardinality of the conditions, I can only imagine them. Please test it and show the results of EXPLAIN to check it.