0

I am new to MYSQL and just learning how to create stored procedures.

I am failing to correctly deal with a NULL value using either IF statement or error handling. Any guidance appreciated.

How can I get this code to execute this result if user enters year and month like this 

call EmployeeYearMonth(1986,2)

SELECT * FROM employees
WHERE YEAR(hire_date) = Num1
AND MONTH(hire_date) = Num2;

and this result if user only enters year like this 

call EmployeeYearMonth(1986)

SELECT * FROM employees
WHERE YEAR(hire_date) = Num1

I have been asked to create a procedure which is able to filter a database according to Year / Month employee was hired. The procedure should be able to work if the user omits the month.

EXAMPLE 1 - I've tried to handle it with an 'IF IS NULL THEN' but when I run call EmployeeYearMonth(1986); I get error 1318

DELIMITER $$
CREATE PROCEDURE EmployeeYearMonth(Num1 INT,Num2 INT)
DETERMINISTIC
BEGIN
IF Num2 IS NULL THEN
SELECT * FROM employees
WHERE YEAR(hire_date) = Num1;
ELSE
SELECT * FROM employees
WHERE YEAR(hire_date) = Num1
AND MONTH(hire_date) = Num2;
END IF;
END$$
DELIMITER ;

EXAMPLE 2 -

I've tried to handle the 1318 error

DELIMITER $$
CREATE PROCEDURE EmployeeYearMonth(Num1 INT, Num2 INT)
BEGIN
-- error code for handling missing parameter in event of missing month
DECLARE EXIT HANDLER FOR 1318
BEGIN
SELECT * FROM employees
WHERE YEAR(hire_date) = Num1;
END;
-- main code to handle finding year and month
SELECT * FROM employees
WHERE YEAR(hire_date) = Num1
AND MONTH(hire_date) = Num2;
END$$
DELIMITER ;
slaakso
  • 8,331
  • 2
  • 16
  • 27
Christopher
  • 427
  • 1
  • 8
  • 18
  • If the procedure has 2 parameters then you have to pass 2 parameters in the call. – P.Salmon Feb 02 '20 at 12:29
  • Thanks P.Salmon , I understand from an old post that MYSQL doesn't allow for optional parameters [link](https://stackoverflow.com/questions/12652241/writing-optional-parameters-within-stored-procedures-in-mysql) There were apparently some work arounds but I couldn't work out how to apply those to my question. – Christopher Feb 02 '20 at 12:42
  • the best you can do is to pass null for whichever parameter you wish to be null - but you still have to pass 2 parameters. (your first lump of code is fine for this) You could pass 1 parameter with comma separated values but then the user would have to remember to pass the parameter as a string (ie enclosed in single quotes) and you would have burst the string in the procedure and check it's validity. – P.Salmon Feb 02 '20 at 12:48
  • A workaround is `call EmployeeYearMonth(1986, NULL)` – Paul Spiegel Feb 02 '20 at 12:49
  • Or use incomplete dates: `call EmployeeYearMonth('1986')` and `call EmployeeYearMonth('1986-02')`. – Paul Spiegel Feb 02 '20 at 12:54
  • Thank you for the contributions. I hadn't realized I could give the user the option to write (1986,NULL) rather than leave blank. Example 1 failed to work with - call EmployeeYearMonth(1986); But as you correctly suggest Example 1 does work with - call EmployeeYearMonth(1986,NULL); I originally tried to handle error with Example2, but my error handling in Example2 failed to work. Any ideas why the error handling can't activate and run query if user only puts one parameter? – Christopher Feb 02 '20 at 13:12
  • The error is raised outside of the procedure - So you cannot handle it inside the procedure. – Paul Spiegel Feb 02 '20 at 13:18
  • Thank you for the advice P & Paul. I am still quite new to this, I hope in time I'll get to understand which errors run inside and outside of procedures. For now I can work with call EmployeeYearMonth(1986,NULL). Much appreciated. – Christopher Feb 02 '20 at 13:42

0 Answers0