Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Unanswered: how to call table name in a proc as a parameter

    hi,

    i have a basic procedure for which the parameter shall be the table name and i could not call the procedure in the call statements/

    Please advise.

    mY CODE GOES SOMETHING LIKE THIS:

    DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `PRC_VALIDATE_LOAN`(P_table varchar(150))
    BEGIN

    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id, v_Season_id,v_teamid int;
    DECLARE v_teams ,v_season,v_FIRST_NAME,V_LAST_NAME varchar(200);
    DECLARE cur1 CURSOR FOR
    SELECT id, teams,season,firstname,lastname,season_id,team_id
    FROM P_table
    where id in (select item_id from LOAN_MASTER where status <> 1 and to_validate = 1
    and item_type = P_table);


    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    open cur1;
    read_loop: LOOP
    FETCH cur1 INTO v_id,v_teams,v_season,v_FIRST_NAME,V_LAST_NAME,v_S eason_id,v_teamid;

    IF done THEN
    LEAVE read_loop;
    END IF;

    if v_teams is not null then
    call PRC_VALIDATE_LOAN_TRANSACTIN(v_id ,v_teams,v_season,v_FIRST_NAME,V_LAST_NAME,p_table );
    end if;

    end LOOP;
    close cur1;
    END $$

    DELIMITER ;

    Now for calling the proceudre iam using this statemtne:

    call PRC_VALIDATE_LOAN('dev_schema'.'master_table');

    where dev_schema is the database name and master_table is the table name.

    but could not execute it .

    Getting the error message:
    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 '.'master_table')' at line 1

    and error Number is 1064.

    Please advise
    Last edited by meetvinessh; 07-12-12 at 14:41.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    The problem here is with the parameters that are being passed into the stored procedure. You are passing 'dev_schema'.'master_table'. Should this not be a single string as 'dev_schema.master_table'?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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