Mysql Prepared Statement using same parameter multiple times

Question

I wrote stored procedure like this:

  DELIMITER $$
   CREATE PROCEDURE searchByTerm(term VARCHAR(300))
    BEGIN
    SET @statment = "Select name,description from products where name like '%?%' OR description like '%?%'";
    PREPARE stmt FROM @statment;
    SET @a = term;
    SET @b = term;
    EXECUTE stmt USING @a,@b;
    DEALLOCATE PREPARE stmt; 
  END$$

calling it as :

CALL searchByTerm('xyz');

it results in following error:

Error Code : 1210
Incorrect arguments to EXECUTE

Am i doing anythng wrong? I know it can be done with concat statement but why it's not working like this? Can't i use same parameter multiple times? Thanks for any help..

Rameez · Accepted Answer · 2012-11-07 05:50:19Z

1

use CONCAT in your query

SET @statment = "Select name,description 
                 from products 
                 where name like CONCAT('%', ? ,'%') OR 
                       description like CONCAT('%', ? ,'%')";

so your full query will look like this,

DELIMITER $$
CREATE PROCEDURE searchByTerm(term VARCHAR(300))
  BEGIN
    SET @statment = "Select name,description 
                     from products 
                     where name like CONCAT('%', ? ,'%') OR 
                           description like CONCAT('%', ? ,'%')";
    PREPARE stmt FROM @statment;
    SET @a = term;
    SET @b = term;
    EXECUTE stmt USING @a,@b;
    DEALLOCATE PREPARE stmt; 
  END$$

edited Nov 7, 2012 at 5:50

Rameez

1,7222 gold badges19 silver badges41 bronze badges

answered Nov 7, 2012 at 5:39

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rameez Over a year ago

great... it worked..many thanks :) it was really a silly mistake that i didn't consider '?' as part of string

Collectives™ on Stack Overflow

Mysql Prepared Statement using same parameter multiple times

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related