MySQL – Dynamic SQL with EXECUTE and PREPARE Statements

MySQL supports Dynamic SQL with the help of EXECUTE and PREPARE statements. Suppose you have a scenario where you need to pass table name as parameter value and returns all column values, you can use Dynamic SQL.

Let us create this table and data.

CREATE TABLE TestTable (ID INT, Col VARCHAR(4));
INSERT INTO TestTable (ID, Col)
SELECT 1, 'A'
UNION ALL
SELECT 1, 'B'
UNION ALL
SELECT 1, 'C'
UNION ALL
SELECT 2, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 2, 'C'
UNION ALL
SELECT 2, 'D'
UNION ALL
SELECT 2, 'E';

Now pass the table name as a parameter and returns all rows from it.

SET @table_name:='TestTable';
SET @sql:=CONCAT('SELECT * FROM ',@table_name);
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;

The variable @table_name is assigned name of the table. The variable @sql forms SELECT statement concatenating the Table name so the actual SELECT statement becomes SELECT * FROM TestTable. The PREPARE statement prepares the statement for execution and assigns a name (in this case it is dynamic_statement). EXECUTE command executes the statement prepared by the prepared name dynamic_statement and produces the result. The DEALLOCATE command releases the prepared statement.

So the result is

MySQL - Dynamic SQL with EXECUTE and PREPARE Statements dynamic

ID Col
 1 A
 1 B
 1 C
 2 A
 2 B
 2 C
 2 D
 2 E

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Keeping MSDB System Database Lean and Fit – Notes from the Field #033
Next Post
SQL SERVER – Fix Error – Package ‘Microsoft SQL Management Studio Package’ failed to load in SQL Server Management Studio

Related Posts

7 Comments. Leave new

  • VERY HELPFUL ….THANKS A LOT

    Reply
  • How do you get the number of rows returned by the update statement after the prepare preprocessing statement in the Mysql stored procedure?

    Reply
  • Hello Sir,I need to insert into table from dynamic query. I have try to use Prepare and Execute statement but its not working. Please help me where i do mistake.

    SET v_SQL = CONCAT(‘SELECT *
    FROM (select PSD.PatientId,PD.MRNNo,PD.Gender,PD.Location,PD.AgeRange, QO.QuestionID,QM.ScreeningType,
    (case when OptionRange CONVERT(VARCHAR(500),-1.0) then CONVERT(nvarchar(200),OptionRange,101) else OptionData end) as OptionData ,
    UM.Name,pa.Score,pa.Quantityfrequency,pa.Harm,pa.dependence,pa.Concern,pa.severity,pa.discrepancy
    from dbo.PatientScreeningDetail PSD
    inner join dbo.QuestionOptions QO on PSD.QuestionOptionID=QO.QuestionOptionID
    inner join dbo.QuestionMaster QM on (QO.QuestionID=QM.QuestionID and QM.ScreeningType=”Alcohol”)
    Inner Join PatientDetail PD on PD.PatientID=psd.PatientID
    inner join Usermaster UM on PD.CoachID=UM.UserId
    inner join PatientAuditResult pa on (pa.PatientID=psd.PatientID and pa.ScreeningType=”Alcohol”)
    ) AS t
    PIVOT (max([OptionData])
    FOR [QuestionID] IN (‘ , v_PivotColumns , ‘)) AS p;’);

    DROP TEMPORARY TABLE IF EXISTS AlcoholTable;
    CREATE TEMPORARY TABLE AlcoholTable ( PatientId int ,MRNNo varchar(500),Gender varchar(500),Location varchar(500),AgeRange varchar(500),ScreeningType Varchar(50),Name longtext,AlcoholScore int,AlcoholQuantityfrequency int,AlcoholHarm int ,Alcoholdependence
    int,AlcoholConcern int ,Alcoholseverity int ,Alcoholdiscrepancy int,ALcoQue1 varchar(500),ALcoQue2 varchar(500),ALcoQue3 varchar(500),ALcoQue4 varchar(500),ALcoQue5 varchar(500),ALcoQue6 varchar(500),ALcoQue7 varchar(500),ALcoQue8 varchar(500),ALcoQue9 varchar(500),ALcoQue10 varchar(500),ALcoQue11 varchar(500),
    ALcoQue12 varchar(500),ALcoQue13 varchar(500));

    Insert into AlcoholTable
    SET stmt_str = v_SQL;
    PREPARE stmt FROM stmt_str;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    Reply
  • C V Kishore Kumar
    October 25, 2020 9:31 am

    Dynamic Insert Statement Sample – PROCEDURE Param (IN tableName varchar(100),IN columnName1 varchar(100),IN columnName2 varchar(100),IN columnValue1 varchar(100),IN columnValue2 varchar(100))

    @dyn_sql=CONCAT(” INSERT INTO “, tableName , “(” , columnName1 , “,”, columnName2 ,”)”,” VALUES “,”(‘” , columnValue1 , “‘,”,”‘”,columnValue2,”‘)”);

    Reply

Leave a Reply

Menu