How to make a Stored Procedure select everything if one of the Arguments that are passed is null?-CodePudding

I'm working on this project that uses databases and I've created a table in MySQL tba_instruments. It has both the ID and the Name of each instrument. It's something like this:

| INSTRUMENT_ID | INSTRUMENT_NM |
| --------      | --------      |
| 1             | Violin        |
| 2             | Cello         |
| 3             | Flute         |
| 4             | Trumpet       |

In order to get the instruments by passing a certain filter, I wrote this Stored Procedure

DELIMITER //
CREATE PROCEDURE GetInstruments_ByFilter(
    IN instrument_id INT, 
        IN instrument_name VARCHAR(255)
        )
BEGIN
    SELECT * FROM tba_instrument 
    WHERE ID_INSTRUMENT = instrument_id
    AND INSTRUMENT_NM = instrument_name;
END //
DELIMITER ;

CALL GetInstruments_ByFilter(1, 'Violin');

RESULT:

| INSTRUMENT_ID | INSTRUMENT_NM |
| 1             | Violin        |

It works just fine if the Paremeters passed match what's in the table. But I want it to behave differently if one the Paremeters is null, e.g:

CALL GetInstruments_ByFilter('Violin');

It prints an error message because one Argument is missing. I'd like that it would then just show the entire table instead of an error message. Is there a way to do that in MySQL?

What I thought so far was just to create a different Stored Procedure for this particular case, but it doesn't look like it's the best solution.

CodePudding user response：

You can take advantage of the IFNULL-function.

DELIMITER //
CREATE PROCEDURE GetInstruments_ByFilter(
instrument_id INT, 
instrument_name VARCHAR(255)
)
BEGIN

SELECT * 
FROM tba_instrument 
WHERE ID_INSTRUMENT = IFNULL(instrument_id, ID_INSTRUMENT) 
   AND INSTRUMENT_NM = IFNULL(instrument_name, INSTRUMENT_NM);
END //
DELIMITER ;

CALL GetInstruments_ByFilter(1, 'Violin');
CALL GetInstruments_ByFilter(1, NULL);
CALL GetInstruments_ByFilter(NULL, 'Violin');