Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: Concat inside Concat to call another procedure

    Hi ,

    I got struct over here , i am getting NULL when i am running this procedure ...I call one procedure , where it has a call to another procedure ,
    below is my proc named test_call ,where it call test_out, an another procedure

    1 ...............

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `test_call` $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `test_call`()
    BEGIN

    /*
    I am getting NULL when i run in and out as param , where as i am getting the result
    when i am running this as in param ..
    */

    # 1. Has in and out param
    set @call2 = concat('call ' ,'test_out(',concat('21','0'),',',@b,')');
    select @call2,@b;

    # 2. Has in param only
    #set @call2 = concat('call ' ,'test1(',concat('21','0'),')');
    #select @call2;


    PREPARE stmt2 FROM @call2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;


    END $$

    DELIMITER ;

    ----------------------------------------------------------------------
    Proc 2
    ----------------------------------------------------------------------
    DELIMITER $$

    DROP PROCEDURE IF EXISTS `test_out` $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `test_out`(in numbersWith2Digit varchar(5),out sa text)
    BEGIN

    set @c = concat('123','456',concat('789'),numbersWith2Digit );
    set sa = @c;
    select sa;

    END $$

    DELIMITER ;
    -------------------------------------------------------------------

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Firstly, test_call is expecting @b before it is called. If you do not set this the result of the concat will be NULL.

    Secondly, the IN OUT parameter must be a variable name and not a value. So in the first procedure test_call() you must define @b inside a string variable

    Code:
    DROP PROCEDURE IF EXISTS `test_call` $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `test_call`()
    BEGIN
    
    /*
    I am getting NULL when i run in and out as param , where as i am getting the result
    when i am running this as in param ..
    */
    
    # 1. Has in and out param
    set @call2 = concat('call ' ,'test_out(',concat('21','0'),',','@b',')');
    select @call2,@b;
    
    PREPARE stmt2 FROM @call2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
    
    
    END $$
    
    DELIMITER ;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2010
    Posts
    2

    Smile

    Hi Ronan,

    Thankyou very much for the reply... Now i understand where i did the mistake .Now code is working properly...

Posting Permissions

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