I receive many emails on, how can I write dynamic SQL in MySQL Stored Procedure.
I understand that the database professional tries 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 this article for Prepared Statements.
|Prepared or Parameterized Statements in Database System|
In this post, I demonstrate dynamic SQL as a prepared statement in MySQL Stored Procedure.
Let’s show me a small example of this:
First, create sample table and data.
CREATE TABLE tbl_TestDynamicSQL
INSERT INTO tbl_TestDynamicSQL
Now create one stored procedure to pass the column name dynamically.
CREATE PROCEDURE usp_GetEmployeeDetailsDynamic
IN ColumnName VARCHAR(255)
SET @SQLText = CONCAT('SELECT ',ColumnName,' FROM tbl_TestDynamicSQL ');
PREPARE stmt FROM @SQLText;
DEALLOCATE PREPARE stmt;
Call this stored procedure by giving desire column name and it will return only that column data.
CALL usp_GetEmployeeDetailsDynamic ('EmpName');
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.