MySQL Store Procedure with Parameter that is a list

Question

I have a MySQL statement as below:

select * from jobs where job_status in (6,9,10) and due_date>'2018-10-31'

I would like to create a store procedure, so that it takes 2 parameters:

job_status

and

due_date

I am not sure how to define the "job_status" parameter. It is a list of integers and can be any number of values. e.g. (1,2,3,4,5) or (1)

Answer 1

You can try by declaring variable like below

DECLARE @LIST VARCHAR(200)
DECLARE @vdate date
SET @LIST = '1,3'
SET @vdate='2018-10-31'

select * from jobs where job_status in (@LIST) and due_date>@vdate

Answer 2

   DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_selectjobs` $$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_selectjobs`(
  IN job_status_in VARCHAR(255),
  IN due_date_in datetime    )
BEGIN
SELECT * FROM jobs
WHERE job_status in (@job_status_in) AND due_date>'@due_date_in'

END $$

DELIMITER ;

you can create stored procedure like this and you can call this stored procedure with your own parameters like

call sp sp_selectjobs('1,2,3,4','2018-11-2') 

like this

Answer 3

In Mysql you have to create store procedure such as:

DELIMITER //
DROP PROCEDURE IF EXISTS up_getJobsByStatusAndDuedate //
CREATE PROCEDURE `up_getJobsByStatusAndDuedate`(pJobStatusList TEXT, pDuedate varchar(10))
BEGIN

    select * from jobs where job_status in (pJobStatusList) and due_date> pDuedate;

END//

Then you can test call store on Workbench:

call up_getJobsByStatusAndDuedate('6,9,10', '2018-10-31');