Geek asks and answers

How to write mysql stored procedure using the same statement

Below is my code:

     DELIMITER $$

     CREATE DEFINER=`root`@`localhost` PROCEDURE `employee_with_asset`(IN name     VARCHAR(250))
     BEGIN
     SELECT a.Asset_code,a.name as name1,a.type,a.description,`purchase date`,
    `amc availability`,`amc renewal`,`employee Id`,b.Name FROM `asset_details` a,
     employee b WHERE  b.Name LIKE '%' + @name + '%' and a.`assigned to`=b.`employee Id`;
     END

Shows an error near LIKE. How to solve it.

+8
source share
3 answers

concatenation in mysql is done using CONCAT()

LIKE CONCAT('%', @name , '%')

FULL STATEMENT

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `employee_with_asset`
(
    IN _name     VARCHAR(250)
)
BEGIN
    SELECT  a.Asset_code,
            a.name as name1,
            a.type,
            a.description,
            `purchase date`,
            `amc availability`,
            `amc renewal`,
            `employee Id`,
            b.Name 
    FROM    `asset_details` a
            INNER JOIN employee b 
                ON a.`assigned to` = b.`employee Id`
    WHERE   b.Name LIKE CONCAT('%', _name , '%');
END $$
DELIMITER ;
+22
source

John Wu's answer is correct, but if you are not using the utf8_general_ci collection, you should drop the collection after the WHERE clause (after each condition, if you have several conditions), as well as collecting the column that you are comparing with

... WHERE name LIKE CONCAT('%', @name , '%') COLLATE utf8_unicode_ci

or several conditions

... WHERE name LIKE CONCAT('%', @name , '%') COLLATE utf8_unicode_ci
OR job LIKE CONCAT('%', @name , '%') COLLATE utf8_unicode_ci ...
+1
source

PHP:

$keyword = $_POST['keyword'];
$response = "call user_search('%$keyword%');";

Mysql:

....where user.name like @keyword;
0
source

More articles:


All Articles