Dec 092009
 

I write a stored procedure as below but always result nothing/incorect.

DELIMITER //

DROP PROCEDURE IF EXISTS get_domain //

CREATE PROCEDURE get_domain (IN domin VARCHAR(64))
BEGIN
	SELECT * FROM domain_senders WHERE domain LIKE '%domin%';
END//

DELIMITER ;

The procedure itself is successfully created.After looking around with google i’ve found the solution.I need second variable to concat/stash %domin%

Here’s the store procedure, and it work like a charm 🙂

DELIMITER //

DROP PROCEDURE IF EXISTS get_domain //

CREATE PROCEDURE get_domain (IN domin VARCHAR(64))
BEGIN
	SET @vardomain := CONCAT('%',LOWER(TRIM(domin)),'%');
	SELECT * FROM domain_senders WHERE domain LIKE @vardomain;
END//

DELIMITER ;

  3 Responses to “How To Use LIKE In MYSQL Stored Procedure Select Statement?”

Comments (3)
  1. thanks for sharing sir>>>>…..

  2. mysql_connect($db_host,$username,$password);
    mysql_select_db($db_name);
    /*$query=”DELIMITER $$”;
    $query .=”CREATE PROCEDURE `getcat`(IN topic_id INT,OUT prim_value INT)”;
    $query .=”BEGIN”;
    $query .=”DECLARE parent_value INT(11);”;
    $query .=”DECLARE prim_value INT(11);”;
    $query .=”SELECT id,parent_id INTO prim_value,parent_value FROM keymind_category WHERE id = topic_id;”;
    $query .=”IF parent_value>0 THEN”;
    $query .=”SET max_sp_recursion_depth=10;”;
    $query .=”CALL getcat(parent_value,@prim_value);”;
    $query .=”ELSE”;
    $query .=”SELECT prim_value;”;
    $query .=”END IF;”;
    $query .=”END$$”;
    $query .=”DELIMITER ;”;*/
    $query=”DELIMITER $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `getcat`(IN topic_id INT,OUT prim_value INT)
    BEGIN
    DECLARE parent_value INT(11);
    DECLARE prim_value INT(11);
    SELECT id,parent_id INTO prim_value,parent_value FROM keymind_category WHERE id = topic_id;
    IF parent_value>0 THEN
    SET max_sp_recursion_depth=10;
    CALL getcat(parent_value,@prim_value);
    ELSE
    SELECT prim_value;
    END IF;
    END$$
    DELIMITER ;”;
    $result=mysql_query($query);

    I have to create recursive stored procedure using mysql_query() but its not working ……………….:):):)
    can anyone please give solution for me?:(:(:(:its very urgent…please help me:(:(

  3. well, i don’t have any solutions for your problem right now, maybe someone else do?

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)


*