CODEWITHSUNDEEP
mysqlstored-procedurescursor
Hitesh Kumar
Hitesh Kumar

Reputation: 653

How to create a cursor in Mysql Within Stored procedure

I have created a stored procedure where I need fetch all the records from complaint table and then loop through it and find out who commented when on the particular complaint. You can assume it as blog application where a blog can have multiple comment. I am getting syntax error in

Declare cur CURSOR statement,

What could be the problem or what I am missing into this.

This is the following message I am getting

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare cur CURSOR for SELECT id,user_id FROM complaint3 WHERE user_id IN( SELEC' at line 22

Below is my procedure

DELIMITER $$

CREATE PROCEDURE `myDB`.`ShowResult`(user_id INT, hours INT,L_Complaint_id INT)
BEGIN
 DECLARE complaint_count INT;
 DECLARE 24Hrs_count INT;
 DECLARE 48Hrs_count INT;
 DECLARE Gr_48Hrs_count INT;
 DECLARE notCommented INT;
 DECLARE agentName VARCHAR(100);
 DECLARE v_agentId INT;
 DECLARE v_userId INT;
 DECLARE lastUserCommentDate DATETIME;
 DECLARE lastInternalUserCommentDate DATETIME;
 DECLARE tempDate INT;
 DECLARE v_complaint_id INT;

 SET 24Hrs_count =0;
 SET 48Hrs_count =0;
 SET Gr_48Hrs_count =0;
 SET notCommented =0;
 SET v_complaint_id =0;

 DECLARE cur CURSOR FOR SELECT id,user_id FROM complaint3 WHERE user_id IN( SELECT id FROM user3 WHERE user_type=0);   


CREATE TEMPORARY TABLE IF NOT EXISTS resultTable (
id MEDIUMINT NOT NULL AUTO_INCREMENT,t_agentId INT,t_agentName  VARCHAR(1000),t_24HrsCount INT,t_48HrsCount INT,t_Gr48HrsCount INT,
t_nullCount INT,PRIMARY KEY (id)) ENGINE = MEMORY;

SELECT COUNT(DISTINCT(id)) INTO complaint_count FROM complaint3 WHERE user_id IN(SELECT id FROM user3 WHERE user_type=0);
OPEN cur;
insert_loop: LOOP

 IF complaint_count > 0 THEN    
   FETCH cur INTO complaint_id,user_id;

      SELECT created_at INTO lastUserCommentDate FROM complaint3_diary WHERE complaint_id=v_complaint_id AND user_id = v_user_id ORDER BY id DESC LIMIT 1;
      SELECT assigned_to INTO v_agentId FROM assignment3 WHERE complaint_id=v_complaint_id AND a.expire_at IS NULL;
      SELECT NAME INTO agentName FROM user3 WHERE id=v_agentId;
      SELECT created_at INTO lastInternalUserCommentDate FROM complaint3_diary WHERE complaint_id=v_complaint_id AND user_id = v_agentId ORDER BY id DESC LIMIT 1;
  SELECT TIMESTAMPDIFF(HOUR, lastInternalUserCommentDate, lastUserCommentDate)  INTO tempDate;

  IF (tempDate >0 && tempDate <= 24) THEN
    SET 24Hrs_count =1;
  ELSEIF (tempDate >24 && tempDate <= 48) THEN
     SET 48Hrs_count = 1;
  ELSEIF (tempDate >48) THEN
     SET Gr_48Hrs_count = 1;
  ELSE
     SET notCommneted = 1;
   END IF;
      INSERT INTO resultTable(t_agentId,t_agentName,t_24HrsCount,t_48HrsCount,t_Gr48HrsCount,t_nullCount) VALUES(v_agentId,agentName,24Hrs_count,48Hrs_count,Gr_48Hrs_count,notCommneted);

ELSE
LEAVE insert_loop;
END IF;
 SET complaint_count = complaint_count - 1;
END LOOP;
CLOSE cur;
    SELECT t_agentId,t_agentName,COUNT(t_24HrsCount),COUNT(t_48HrsCount),COUNT(t_Gr48HrsCount),COUNT(t_nullCount) FROM resultTable GROUP BY agentId;

END$$

DELIMITER ;

Upvotes: 1

Views: 1106

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

As per documentation on DECLARE

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

In your code, the statement 

DECLARE cur CURSOR FOR 
    SELECT id, user_id 
      FROM complaint3 
     WHERE user_id IN( SELECT id FROM user3 WHERE user_type = 0 );

has no followed the declaration order rule. And hence is the error.

Change part of your code as following: 

BEGIN
 DECLARE complaint_count INT;
 DECLARE 24Hrs_count INT;
 DECLARE 48Hrs_count INT;
 DECLARE Gr_48Hrs_count INT;
 DECLARE notCommented INT;
 DECLARE agentName VARCHAR(100);
 DECLARE v_agentId INT;
 DECLARE v_userId INT;
 DECLARE lastUserCommentDate DATETIME;
 DECLARE lastInternalUserCommentDate DATETIME;
 DECLARE tempDate INT;
 DECLARE v_complaint_id INT;

 DECLARE cur CURSOR FOR 
     SELECT id, user_id 
       FROM complaint3 
      WHERE user_id IN( SELECT id FROM user3 WHERE user_type = 0 );   

 SET 24Hrs_count =0;
 SET 48Hrs_count =0;
 SET Gr_48Hrs_count =0;
 SET notCommented =0;
 SET v_complaint_id =0;

Upvotes: 3

Related Questions