This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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.
Please visit below article on Prepared Statements:
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'); |
