'Return two separate queries in a stored procedure with MySQL

I'm trying to write a stored procedure in MySQL that will query a table based on the employee's department number. If the department number is present in the table, it should return only those employees belonging to that department. On the other hand, if it's not present in the table, then I want to return all records from the table.

However, I'm not sure how to accomplish this.

Table Schema:

empNo empName salary deptNo
number name salary dept number

My stored procedure so far:

DELIMITER //

CREATE PROCEDURE GetEmpData(
        IN deptNum INT
)
BEGIN
    IF deptNum THEN
        SELECT * FROM empdemo
        WHERE deptNo = deptNum;
    ELSE
        SELECT * FROM empdemo;
    END IF;
END //

I used the if else because I didn't know of any other way to break this query down. If anyone has any other suggestions please post them!

mysqlsqlstored-procedures


Solution 1:[1]

It seems that you need in this query:

SELECT *
FROM empdemo
WHERE empdemo.deptNo = deptNum
   OR NOT EXISTS ( SELECT NULL
                   FROM empdemo
                   WHERE empdemo.deptNo = deptNum )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Akina

Related Questions