How to write mysql stored procedure using like operator
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 ;
The answer of John Woo is right but if you don't use utf8_general_ci collection, you must cast collection after WHERE clause (after each condition if you have multiple conditions) as the same as collection of the column you compare to like this
... WHERE name LIKE CONCAT('%', @name , '%') COLLATE utf8_unicode_ci
or multiple conditions
... WHERE name LIKE CONCAT('%', @name , '%') COLLATE utf8_unicode_ci
OR job LIKE CONCAT('%', @name , '%') COLLATE utf8_unicode_ci ...