CODEWITHSUNDEEP
mysqlstored-procedures
r0m4n
r0m4n

Reputation: 3512

MySQL Procedure with Cursor Parameter

I have the following code that is supposed to return a list of customers that are younger than the provided age parameter and a total count of found records.

My table consists of: columns ID, FIRST_FIRSTNAME, LASTNAME, DATE_OF_BIRTH

I could have easily made a few mistakes in the syntax, logic and formatting of this procedure but forgive me, I am new to this! The following is my sql procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS sample_procedure$$

CREATE PROCEDURE sample_procedure(IN actualinput INT)
BEGIN   
DECLARE no_more_customers int(4);
DECLARE l_customer_count int(4);
DECLARE l_id varchar(1);
DECLARE l_first_name varchar(10);
DECLARE l_last_name varchar(10);
DECLARE l_date_of_birth varchar(20);
DECLARE customer_list varchar(250);

DECLARE dateinput DATE;
SET dateinput=DATE_SUB(now(), interval actualinput year);


DECLARE cid CURSOR FOR
    SELECT ID, FIRST_FIRSTNAME, LAST_NAME, DATE_OF_BIRTH
    FROM customers WHERE DATE_OF_BIRTH >= dateinput;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_customers=1;

SET no_more_customers=0;
SET l_customer_count=0;


OPEN cid;
cid_cursor: REPEAT
    FETCH cid INTO l_id, l_first_name, l_last_name, l_date_of_birth;            IF no_more_customers THEN
            LEAVE cid_cursor;
        END IF;
        SET customer_list=concat(customer_list, l_id,',',l_first_name,',',l_last_name,',',l_date_of_birth);
        SET l_customer_count=1+l_customer_count;

UNTIL no_more_customers
END REPEAT cid_cursor;
CLOSE cid;
SET no_more_customers=0;
SELECT customer_list AS Customers;
SELECT concat(l_customer_count);
END;
$$

I appear to have an error in the method I used to calculate the age of customers, possibly an issue with the method I used to call the cursor and my final list of customers is only returning null. Thanks in advance...

Upvotes: 0

Views: 11325

Answers (1)

Devart
Devart

Reputation: 122040

I can suppose that variable 'customer_list' should be initialized, e.g. - 

...
DECLARE customer_list VARCHAR(250) DEFAULT '';
...

because 'SET customer_list = CONCAT(customer_list, ...)' when customer_list is NULL, will return NULL too.

Take advantage of MySQL Stored Procedure Debugger. Hope this feature will help you.

Upvotes: 3

Related Questions