CODEWITHSUNDEEP
mysql
Rahul Kaushik
Rahul Kaushik

Reputation: 1464

how to use join into a mysql function

I have a function below:

DELIMITER $$
DROP FUNCTION IF EXISTS `vmtest`.`hello2`$$
CREATE DEFINER = `root`@`localhost` FUNCTION `vmtest`.`hello2` (s CHAR(20)) 
RETURNS TEXT
BEGIN 
    DECLARE RETURNID INTEGER;
    DECLARE RETURNNAME TEXT;
    DECLARE PARENTID INTEGER;
    DECLARE RTN TEXT;
    DECLARE RTNNAME TEXT;
    SET RTN = "";
    SET PARENTID = s;

    WHILE PARENTID > 0 DO
        SELECT 
        t1.category_parent_id INTO RETURNID,
        t2.category_name INTO RETURNNAME  
  FROM e255g_virtuemart_category_categories AS t1
        INNER JOIN e255g_virtuemart_categories_de_de AS t2 on t1.id = t2.virtuemart_category_id
        WHERE t1.id = PARENTID;
        IF RETURNID >0 THEN
            IF RTN ="" THEN
                SET RTN = RETURNID;
            ELSE
                SET RTN = CONCAT(RTN, ',', RETURNID);
                SET RETURNNAME = CONCAT(RTNNAME, ',', RETURNNAME);
            END IF;
        END IF;
        SET PARENTID = RETURNID;            
    END WHILE;
    RETURN RETURNNAME;
END$$

DELIMITER ;

I am trying to use join query into the function but its giving me error as below:

Error in query (1327): Undeclared variable: t2

Please help

Upvotes: 0

Views: 37

Answers (1)

Drew
Drew

Reputation: 24959

It is a little sensitive with multiple INTO variables. It would merely be:

DELIMITER $$
DROP FUNCTION IF EXISTS `vmtest`.`hello2`$$
CREATE DEFINER = `root`@`localhost` FUNCTION `vmtest`.`hello2` (s CHAR(20)) 
RETURNS TEXT
BEGIN 
    DECLARE RETURNID INTEGER;
    DECLARE RETURNNAME TEXT;
    DECLARE PARENTID INTEGER;
    DECLARE RTN TEXT;
    DECLARE RTNNAME TEXT;
    SET RTN = "";
    SET PARENTID = s;

    WHILE PARENTID > 0 DO
        SELECT 
        t1.category_parent_id,t2.category_name INTO RETURNID,RETURNNAME  
        FROM e255g_virtuemart_category_categories AS t1
        INNER JOIN e255g_virtuemart_categories_de_de AS t2 
        on t1.id = t2.virtuemart_category_id
        WHERE t1.id = PARENTID;
        IF RETURNID >0 THEN
            IF RTN ="" THEN
                SET RTN = RETURNID;
            ELSE
                SET RTN = CONCAT(RTN, ',', RETURNID);
                SET RETURNNAME = CONCAT(RTNNAME, ',', RETURNNAME);
            END IF;
        END IF;
        SET PARENTID = RETURNID;            
    END WHILE;
    RETURN RETURNNAME;
END$$

DELIMITER ;

So it is ... INTO var1,var2, ...

Upvotes: 1

Related Questions