jayant kumar jayant kumar - 6 months ago 33
SQL Question

How to write mysql stored procedure using like operator

Following 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


It is showing error near LIKE. How to solve it.

Answer

the 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 ;
Comments