How to write Dynamic SQL Query in MySQL Stored Procedure

I received many emails on, how can I write dynamic SQL in MySQL Stored Procedure.

I understood that the database professional tried to find Dynamic SQL in MySQL like SQL Server or PostgreSQL.

MySQL Doesn’t Support dynamic SQL in Stored Procedure. Even dynamic SQL is not good for database security because of SQL Injections.
You have to write prepared statement for this requirement.

In this post, I am sharing demonstration on a dynamic SQL as a prepared statement in the MySQL Stored Procedure.

First, create a sample table and data

1 2 3 4 5 6 7 8

CREATE TABLE tbl_TestDynamicSQL (     EmpID INTEGER     ,EmpName VARCHAR(255)     );   INSERT INTO tbl_TestDynamicSQL VALUES (1,'ABC'),(2,'XYZ'),(3,'PQR'),(4,'RFQ');

Now, create a stored procedure and pass the column name dynamically:

1 2 3 4 5 6 7 8 9 10 11 12

DELIMITER $$ CREATE PROCEDURE usp_GetEmployeeDetailsDynamic ( IN ColumnName VARCHAR(255) ) BEGIN     SET @SQLText = CONCAT('SELECT ',ColumnName,' FROM tbl_TestDynamicSQL ');         PREPARE stmt FROM @SQLText;     EXECUTE stmt;     DEALLOCATE PREPARE stmt; END$$ DELIMITER ;

Call this stored procedure by giving desire column name and it will return only data for that column:

1	CALL usp_GetEmployeeDetailsDynamic ('EmpName');