How to get MYSQL dynamic query using stored procedure?

Question

I am trying to run the dynamic query using a stored procedure in which I am sending the where condition case for example.

My sample stored procedure is shown here:

CREATE PROCEDURE `Storedproc`(IN getwhereconditon varchar(1000))
    BEGIN
    set @param=getwhereconditon ;
    SET @S=concat('Select * from table where (1)',@param);
    PREPARE stmt1 FROM @s;
    EXECUTE stmt1;
    END

Here my stored procedure's name is Storedproc in which I am passing the where condition details and when I call my stored procedure I am getting this error

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0tablecol=1' at line

Show the real value you are passing.And what`s with the (1)? — Mihai
– Mihai, Commented Jun 28, 2014 at 10:27
@Mihai where(1) is the condition if none of the value of comes it will run without an error Storeproc(and nqh.nqh_quarry_id=2) — Singhboy
– Singhboy, Commented Jun 28, 2014 at 10:36
why are you using and in Storeproc(and nqh.nqh_quarry_id=2) — ashok_p
– ashok_p, Commented Jun 28, 2014 at 10:39
The error message doesnt seem to be for the parameter above.Also you shoud have quotes for your parameter. — Mihai
– Mihai, Commented Jun 28, 2014 at 10:49
there is problem in my SP which i Can't able to identify it and yes i am send the parameter in a quotes.If you got any better solution in my In case of my SP then help me out — Singhboy
– Singhboy, Commented Jun 28, 2014 at 10:53

peterm · Accepted Answer · 2014-06-28 13:36:32Z

3

Try

DELIMITER //
CREATE PROCEDURE myproc(IN _where VARCHAR(512))
BEGIN
  SET @sql = 'SELECT * FROM table1';
  IF COALESCE(_where, '') <> '' THEN
    SET @sql = CONCAT(@sql, ' WHERE ', _where);
  END IF;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

or

DELIMITER //
CREATE PROCEDURE myproc(IN _where VARCHAR(512))
BEGIN
  SET @sql = CONCAT('SELECT * FROM table1', COALESCE(CONCAT(' WHERE ', NULLIF(_where, '')), ''));
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Sample usage:

CALL myproc(NULL);
CALL myproc('name=''John''');
CALL myproc('age > 25');
CALL myproc('age < 28 OR age > 30');

Here is SQLFiddle demo

answered Jun 28, 2014 at 13:36

peterm

93k16 gold badges157 silver badges162 bronze badges

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

Collectives™ on Stack Overflow

How to get MYSQL dynamic query using stored procedure?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related