List:General Discussion« Previous MessageNext Message »
From:Timothy R Peterson Date:February 14 2013 9:49pm
Subject:RE: Please check the stored procedure
View as plain text  
To adjust a table name within a SQL statement, you need to create a
string with the updated values and use PREPARE/EXECUTE
See below, I am replacing your CONCAT with the complete insert statement
The example below is also assuming the value in the WHERE clause should
be adjusted to the number, instead of hard-coded to '9'


        set @str = concat('Insert Into test (Panel_Id) select Panel_Id
from ',Project_Number_val,'_List where Project_Number_val='
			,"'",Project_Number_val,"'");
        prepare stmt from @str;
        execute stmt;
        deallocate prepare stmt;

-----Original Message-----
From: Girish Talluru [mailto:girish.dev1986@stripped] 
Sent: Tuesday, February 12, 2013 1:37 AM
To: mysql@stripped
Subject: Please check the stored procedure

DROP PROCEDURE IF EXISTS Cursor_Test;# MySQL returned an empty result
set
(i.e. zero rows).

DELIMITER $$

CREATE PROCEDURE Cursor_Test()
BEGIN

  DECLARE Project_Number_val VARCHAR( 255 );
  DECLARE Project_List_val VARCHAR(255);



  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;


  DECLARE projects_cur CURSOR FOR
    SELECT Project_Id
    FROM Project_Details;


  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;


  OPEN projects_cur;
  select FOUND_ROWS() into num_rows;

  the_loop: LOOP

    FETCH  projects_cur
    INTO   Project_Number_val;


    IF no_more_rows THEN
        CLOSE projects_cur;
        LEAVE the_loop;
    END IF;

SET Project_List_val=CONCAT(`Project_Number_val`,'_List') ----> Please
check am I doing CONCAT correct here?
Insert Into test (Panel_Id) select Panel_Id from Project_List_val where
Project_Number_val='9';  --->Is this taking 9_List as table name?

    SET loop_cntr = loop_cntr + 1;
  END LOOP the_loop;


  select num_rows, loop_cntr;


END $$# MySQL returned an empty result set (i.e. zero rows).


DELIMITER

This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.

Thread
Please check the stored procedureGirish Talluru12 Feb
  • RE: Please check the stored procedureTimothy R Peterson14 Feb