SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2007
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CURSOR Syntax error

    im having trouble with this function that i created, it always says that there's an error in my query but i can't seem to find it,can anyone help me?

    here's my function:
    Code:
    DELIMITER $$
    
    DROP FUNCTION IF EXISTS `feu`.`get_grade_stat`$$
    
    CREATE FUNCTION `get_grade_stat`(p_grade varchar(255),p_inst_code varchar(255)) RETURNS varchar(255) CHARSET latin1
    BEGIN
    	declare v_num_grade varchar(255);
    	declare v_grade_status  varchar(255);
    	declare v_grsc_code varchar(255);
    	
    	BEGIN
    	    SELECT grsc_code INTO v_grsc_code FROM institutes WHERE inst_code = p_inst_code;
    	END;
    
    	set v_grade_status = 'N';
    
    DECLARE prodrecord CURSOR FOR 
    	SELECT 
    		char_value,
    		from_num_value,
    		to_num_value,
    		credit_type 
    	FROM grade_details WHERE grsc_code = v_grsc_code;
    
    	declare done int default 0;
    	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    
    	declare v_char_value varchar(255);
    	declare v_from_num_value  varchar(255);
    	declare v_to_num_value varchar(255);
    	declare v_credit_type varchar(255);
    
    	OPEN prodrecord;
    
    		while not done do 
    			fetch prodrecord into v_char_value,v_from_num_value,v_to_num_value,v_credit_type;
    
    			if v_char_value is not null then
    				IF p_grade = v_char_value THEN
    					IF v_credit_type = 'P' THEN
    					  set v_grade_status = 'P';
    					ELSEIF v_credit_type = 'F' THEN
    					  set v_grade_status = 'F';
    					ELSE
    					  set v_grade_status = 'N';
    					END IF;
    				LEAVE;
    				END IF;
    			ELSE
    				set v_num_grade = p_grade;
    				IF v_num_grade BETWEEN v_from_num_value AND ifnull(v_to_num_value, v_from_num_value) THEN
    				  IF v_credit_type = 'P' THEN
    				    set v_grade_status = 'P';
    				  ELSEIF v_credit_type = 'F' THEN
    				    set v_grade_status = 'F';
    				  ELSE
    				    set v_grade_status = 'N';
    				  END IF;
    				LEAVE;
    				EXIT;
    				END IF;
    			end if;
    	end while;		
    	
    	CLOSE prodrecord;
      RETURN (v_grade_status);
    END$$
    
    DELIMITER ;


    and here's is the error that always shows

    Code:
    (0 row(s)affected)
    (0 ms taken)
    
    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 prodrecord CURSOR FOR 
    	SELECT 
    		char_value,
    		from_num_value,
    		to_num' at line 11
    (0 ms taken)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you don't need a cursor for this

    a simple query will do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2007
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok,i tried something like this but it still shows an error...

    Code:
    DELIMITER $$
    
    DROP FUNCTION IF EXISTS `feu`.`get_grade_stat`$$
    
    CREATE FUNCTION `get_grade_stat`(p_grade varchar(15),p_inst_code varchar(15)) RETURNS varchar(150) CHARSET latin1
    BEGIN
    	declare v_num_grade varchar(255);
    	declare v_grade_status  varchar(100);
    	declare v_grsc_code varchar(50);
    	
    	declare v_char_value varchar(255);
    	declare v_from_num_value  varchar(255);
    	declare v_to_num_value varchar(255);
    	declare v_credit_type varchar(255);
    	
    	BEGIN
    	    SELECT grsc_code
    	      INTO v_grsc_code
    	      FROM institutes
    	     WHERE inst_code = p_inst_code;
    	END;
    	
    	set v_grade_status = 'N';
    	
    	SELECT char_value,
    		from_num_value,
    		to_num_value,
    		credit_type 
    	into 
    		v_char_value,
    		v_from_num_value,
    		v_to_num_value,
    		v_credit_type 
    	FROM grade_details WHERE grsc_code = v_grsc_code;
    
    	if v_char_value is not null then
    	IF p_grade = v_char_value THEN
    		IF v_credit_type = 'P' THEN
    		  set v_grade_status = 'P';
    		ELSEIF v_credit_type = 'F' THEN
    		  set v_grade_status = 'F';
    		ELSE
    		  set v_grade_status = 'N';
    		END IF;
            END IF;
    ELSE
            set v_num_grade = p_grade;
            IF v_num_grade BETWEEN v_from_num_value AND ifnull(v_to_num_value, v_from_num_value) THEN
              IF v_credit_type = 'P' THEN
                set v_grade_status = 'P';
              ELSEIF v_credit_type = 'F' THEN
                set v_grade_status = 'F';
              ELSE
                set v_grade_status = 'N';
              END IF;
            END IF;
    end if;
      RETURN (v_grade_status);
    END$$
    
    DELIMITER ;
    the SELECT INTO statement returns multiple rows so it shows an error message, i wanted to use a CURSOR statement so that it show the result one by one


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •