CODEWITHSUNDEEP
mysqlsqlstored-procedures
Shann
Shann

Reputation: 690

mysql dynamic query in stored procedure

i am creating a dynamic query in stored procedure. my stored procedure is as follows:

CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40),IN w_team VARCHAR(40))
BEGIN
SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team=",w_team);
 PREPARE stmt3 FROM @t1;
 EXECUTE stmt3;
 DEALLOCATE PREPARE stmt3;
END

when i try to run it with the following call:

call test1 ('Test','SPA');

i get the following error message:

Error Code: 1054. Unknown column 'SPA' in 'where clause'

i tested without where condition and it works fine, but with the where condition its not working, i tried using @ with the variable name but it still does not work.

Thanks for your help.

Upvotes: 12

Views: 66010

Answers (4)

Raging Bull
Raging Bull

Reputation: 18737

You missed to enclose the parameter w_team in WHERE clause.

Try like this:

SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team='",w_team,"'");

Explanation:

Query from your code would be like:

SELECT * FROM Test where team=SPA

It will try find a column SPA which is not available, hence the error.

And we changed it to:

SELECT * FROM Test where team='SPA'

Upvotes: 11

Raman Singh
Raman Singh

Reputation: 75

you can add dynamic fields and condition by using CONCAT() MySQL function. I checked this is working fine.

 DELIMITER $$
 /*define procedure name*/
 CREATE PROCEDURE getSearchData()
 BEGIN
 
 DECLARE conditions varchar(1000);
 DECLARE selectField varchar(1000);
 DECLARE SQL_QUERY  varchar(1000);   

 /*define  default select and condition*/
 SET @selectField = 'status,id';
 set @conditions = ' where return_flight=0'; 

 SET @SQL_QUERY = CONCAT('SELECT ',@selectField, ' FROM flights ',@conditions);   
/* you can add more select fields and conditions according to your requirement */ 

PREPARE stmt1 FROM @SQL_QUERY ;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 1549

Try this..

CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40),IN w_team VARCHAR(40))
BEGIN
SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team='",w_team,"'");
 PREPARE stmt3 FROM @t1;
 EXECUTE stmt3;
 DEALLOCATE PREPARE stmt3;
END

You are missing quotes around w_team variable..

you should print the statement that dynamically build so you can just copy printed statement and try so you can easily find this kind of problem.

select @t1 will print the statment that build dynamically..

Upvotes: 6

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

Error Code: 1054. Unknown column 'SPA' in 'where clause'

This happens when you do not enclose input string within quotes, and SQL engine tries to identify it as a column in the table being queried. But it fails as it can't find it.

But what happens when it finds such column?
It fetches results when it finds some matches on the column values.
Obviously this is not what one was expecting.

How to overcome this? Use Prepared Statements with dynamic input values.

You can use placeholders like ? in stored procedures too on dynamic input values to use with Prepared Statements. The engine will handle escape characters and other string values when assigned to or compared within SQL expressions.

You just need to re-assign procedure inputs to one or more session variables, as required.

Example on your procedure: 

CREATE PROCEDURE `test1`( IN tab_name VARCHAR(40), IN w_team VARCHAR(40) )
BEGIN
  SET @t1 = CONCAT( 'SELECT * FROM ', tab_name, ' where team = ?' ); -- <-- placeholder
  SET @w_team := w_team;

  PREPARE stmt3 FROM @t1;
  EXECUTE stmt3 USING @w_team; -- <-- input for placeholder
  DEALLOCATE PREPARE stmt3;
END;

Upvotes: 15

Related Questions