Query results are empty, output variable is correct

Question

I am creating a stored procedure (routine) that selects multiple fields from a table and then sets a check/output variable.

Based on the data in my database, the check/output variable sets appropriately to the data being asked for, but my query does not return any actual data:

CREATE DEFINER=`odcorg_darrmik`@`%` PROCEDURE `sp_Get_Specific_Load`(
IN LOAD_ID INT,
OUT SUCCESS BIT)
BEGIN
set SUCCESS = 0;
SELECT 
       LOAD_ID,
       DRIVER_ID,
       BOL_NUMBER,
       MILES,
       LOAD_PAY,
       SURCHARGE,
       TARP_FEE,
       DESCRIPTION,
       WEIGHT, 
       TRUCK_NUMBER,
       TRAILER_NUMBER, 
       HAZARDOUS, 
       DATE_RECEIVED, 
       DATE_DISPATCHED,
       DATE_DUE, 
       DATE_DELIVERED,
       BROKER_COMPANY,
       BROKER_NAME,
       TIME_APPOINTMENT,
       TIME_ARRIVED,
       TIME_STARTED,
       TIME_FINISHED,
       FCFS,
       COMPLETE,
       PAID_COMPANY,
       PAID_DRIVER,
       (SELECT NAME FROM customers inner join loads_info on loads_info.LOAD_ID = @LOAD_ID and customers.CUSTOMER_ID = loads_info.SHIPPER_ID) AS SHIPPER_NAME,
       SHIPPER_ID,
       (SELECT NAME FROM customers INNER JOIN loads_info ON loads_info.LOAD_ID = @LOAD_ID AND customers.CUSTOMER_ID = loads_info.CONSIGNEE_ID) AS CONSIGNEE_NAME,
       CONSIGNEE_ID,
       (SELECT FIRST_NAME + ' ' + LAST_NAME FROM employee inner join loads_info on loads_info.LOAD_ID = @LOAD_ID and EMPLOYEE_ID = DRIVER_ID) AS DRIVER_NAME, 
       (SELECT SIGNED_BOL FROM loads_documents INNER JOIN loads_info ON loads_info.LOAD_ID = @LOAD_ID and loads_documents.LOAD_ID = @LOAD_ID) AS SIGNED_BOL
FROM loads_info WHERE LOAD_ID = @LOAD_ID;
set SUCCESS = 1;
END

I have gone so far as to strip most everything from the query:

CREATE PROCEDURE sp_Get_Specific_Load(
IN LOAD_ID INT,
OUT SUCCESS BIT)
BEGIN
set SUCCESS = 0;
SELECT * FROM loads_info;
set SUCCESS = 1;
END

The above procedure will again return the SUCCESS output, but doesn't return anything from the

SELECT * FROM loads_info;

If I remove EVERYTHING leaving only the

SELECT * FROM loads_info;

The procedure will return data...if I add a WHERE clause (to the very simple query just above) I once again get no data...all the field headers but no data.

I am very new to MySQL... I converted this procedure from one that I wrote in MSSQL that functions as expected, returning the requested data and the check/output variable.

What have I done wrong??

Answer 1

It's your use of variables that you need to look at. The first thing to note is that @LOAD_ID in your WHERE clause is NOT the LOAD_ID you call your procedure with as the following example shows. @LOAD_ID is a completely separate variable and, since it's never set to anything, its value is NULL. The WHERE clause is therefore testing LOAD_ID = NULL, which will never be true, hence no rows are returned.

mysql> CREATE PROCEDURE `sp_Get_Specific_Load`(IN LOAD_ID INT, OUT SUCCESS BIT)
    -> BEGIN
    -> set SUCCESS = 0;
    -> SELECT LOAD_ID, @LOAD_ID;
    -> set SUCCESS = 1;
    -> END //

mysql> SET @SUCCESS = 0;
mysql> CALL `sp_Get_Specific_Load` (999, @SUCCESS);
+---------+----------+
| LOAD_ID | @LOAD_ID |
+---------+----------+
|     999 | NULL     |
+---------+----------+
1 row in set (0.01 sec)

mysql> SELECT @SUCCESS;
+----------+
| @SUCCESS |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

The second thing to avoid is using parameter names which match your column names. If you use the same name, MySQL pick the input parameter over the column name. As the next example shows it will pick the parameter, effectively testing the parameter value against itself (true for every row). So, you need to rename your input parameter to distinguish between the two.

mysql> CREATE TABLE LOADS_INFO (
    ->   LOAD_ID INT
    -> );  
mysql> INSERT INTO LOADS_INFO (LOAD_ID) VALUES (1),(2),(3),(4),(5),(999);

mysql> CREATE PROCEDURE `sp_Get_Specific_Load`(IN LOAD_ID INT, OUT SUCCESS BIT)
    -> BEGIN
    -> set SUCCESS = 0;
    -> SELECT * FROM LOADS_INFO WHERE LOAD_ID = LOAD_ID;
    -> set SUCCESS = 1;
    -> END //

-- Matches ALL records with input of 999
mysql> CALL `sp_Get_Specific_Load` (999, @SUCCESS);
+---------+
| LOAD_ID |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|     999 |
+---------+
6 rows in set (0.00 sec)