Stored Procedure Not working as Expected When I create the stored procedure without parameter it works as expected, but when I create it with parameter  it doesn't return any row.

DELIMITER //

CREATE PROCEDURE search()
BEGIN 
SELECT * FROM locality WHERE (LOWER( REPLACE(  `locality_area` ,  ' ',  '' ) ) REGEXP ('^.* GS Road.*$')) UNION ALL SELECT * FROM locality WHERE (`locality_area` REGEXP ('^.* GS Road.*$'));

END//


The above procedure returns the rows.

But this doesn't return any value

DELIMITER //

CREATE PROCEDURE search(IN **qry** varchar(100))
BEGIN 
SELECT * FROM locality WHERE (LOWER( REPLACE(  `locality_area` ,  ' ',  '' ) ) REGEXP ('^.* ***qry**.*$')) UNION ALL SELECT * FROM locality WHERE (`locality_area` REGEXP ('^.* ***qry**.*$'));

END//
That's because you're looking for the string 'qry' instead of the value held by the parameter qry.

Replace:

REGEXP ('^.* qry.$')) 


With

REGEXP CONCAT('^.* ',qry, '.$'))