mysql dynamic query in stored procedure

by

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;

More Related Contents:

  1. Connect By Prior Equivalent for MySQL
  2. MySQL create stored procedure syntax with delimiter
  3. MySQL : transaction within a stored procedure
  4. MySQL Stored Procedures not working with SELECT (basic question)
  5. Split a string and loop through values in MySQL stored procedure
  6. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  7. What is dynamic SQL?
  8. INSERT INTO SELECT statement with more columns which is not in another table
  9. MySQL how to fill missing dates in range?
  10. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  11. How to split the name string in mysql?
  12. Why does MySQL allow “group by” queries WITHOUT aggregate functions?
  13. Cumulative sum over a set of rows in mysql
  14. How to set a maximum execution time for a mysql query?
  15. Cast int to varchar
  16. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  17. SQL query of multiple values in one cell
  18. Import SQL file into mysql
  19. MySQL SELECT last few days?
  20. “where 1=1” statement [duplicate]
  21. How do you debug MySQL stored procedures?
  22. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  23. how to cast the hexadecimal to varchar(datetime)?
  24. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  25. How to get a list of months between two dates in mysql
  26. Postgres LEFT JOIN with WHERE condition
  27. How to format number with “.” as thousands separator and “,” as decimal separator?
  28. How to check if field is null or empty in MySQL?
  29. Convert SQL Server query to MySQL [duplicate]
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment