How To have Dynamic SQL in MySQL Stored Procedure

Question

How do you build and use dynamic sql in a MySQL stored procedure

User · Answer

You can pass thru outside the dynamic statement using User-Defined Variables  Server version  5 6 25-log MySQL Community Server  GPL   mysql gt  PREPARE stmt FROM  select  AAAA  into  a   Query OK  0 rows affected  0 01 sec  Statement prepared  mysql gt  EXECUTE stmt  Query OK  1 row affected  0 01 sec   DEALLOCATE prepare stmt  Query OK  0 rows affected  0 01 sec   mysql gt  select  a   ------     a      ------   AAAA     ------  1 row in set  0 01 sec

User · Answer

After 5 0 13  in stored procedures  you can use dynamic SQL   delimiter     CREATE PROCEDURE dynamic IN tbl CHAR 64   IN col CHAR 64   BEGIN     SET  s   CONCAT  SELECT   col   FROM   tbl        PREPARE stmt FROM  s      EXECUTE stmt      DEALLOCATE PREPARE stmt  END    delimiter     Dynamic SQL does not work in functions or triggers  See the MySQL documentation for more uses

User · Answer

You can pass thru outside the dynamic statement using User-Defined Variables  Server version  5 6 25-log MySQL Community Server  GPL   mysql gt  PREPARE stmt FROM  select  AAAA  into  a   Query OK  0 rows affected  0 01 sec  Statement prepared  mysql gt  EXECUTE stmt  Query OK  1 row affected  0 01 sec   DEALLOCATE prepare stmt  Query OK  0 rows affected  0 01 sec   mysql gt  select  a   ------     a      ------   AAAA     ------  1 row in set  0 01 sec

User · Answer

I don t believe MySQL supports dynamic sql   You can do  prepared  statements which is similar  but different   Here is an example   mysql gt  PREPARE stmt FROM      - gt   select count         - gt  from information schema schemata      - gt  where schema name     or schema name        Query OK  0 rows affected  0 00 sec  Statement prepared mysql gt  EXECUTE stmt      - gt  USING  schema1  schema2  ----------    count       ----------           2    ----------  1 row in set  0 00 sec  mysql gt  DEALLOCATE PREPARE stmt    The prepared statements are often used to see an execution plan for a given query   Since they are executed with the execute command and the sql can be assigned to a variable you can approximate the some of the same behavior as dynamic sql   Here is a good link about this   Don t forget to deallocate the stmt using the last line   Good Luck

User · Answer

I don t believe MySQL supports dynamic sql   You can do  prepared  statements which is similar  but different   Here is an example   mysql gt  PREPARE stmt FROM      - gt   select count         - gt  from information schema schemata      - gt  where schema name     or schema name        Query OK  0 rows affected  0 00 sec  Statement prepared mysql gt  EXECUTE stmt      - gt  USING  schema1  schema2  ----------    count       ----------           2    ----------  1 row in set  0 00 sec  mysql gt  DEALLOCATE PREPARE stmt    The prepared statements are often used to see an execution plan for a given query   Since they are executed with the execute command and the sql can be assigned to a variable you can approximate the some of the same behavior as dynamic sql   Here is a good link about this   Don t forget to deallocate the stmt using the last line   Good Luck

User · Answer

After 5 0 13  in stored procedures  you can use dynamic SQL   delimiter     CREATE PROCEDURE dynamic IN tbl CHAR 64   IN col CHAR 64   BEGIN     SET  s   CONCAT  SELECT   col   FROM   tbl        PREPARE stmt FROM  s      EXECUTE stmt      DEALLOCATE PREPARE stmt  END    delimiter     Dynamic SQL does not work in functions or triggers  See the MySQL documentation for more uses

User · Answer

I don t believe MySQL supports dynamic sql   You can do  prepared  statements which is similar  but different   Here is an example   mysql gt  PREPARE stmt FROM      - gt   select count         - gt  from information schema schemata      - gt  where schema name     or schema name        Query OK  0 rows affected  0 00 sec  Statement prepared mysql gt  EXECUTE stmt      - gt  USING  schema1  schema2  ----------    count       ----------           2    ----------  1 row in set  0 00 sec  mysql gt  DEALLOCATE PREPARE stmt    The prepared statements are often used to see an execution plan for a given query   Since they are executed with the execute command and the sql can be assigned to a variable you can approximate the some of the same behavior as dynamic sql   Here is a good link about this   Don t forget to deallocate the stmt using the last line   Good Luck

User · Answer

I don t believe MySQL supports dynamic sql   You can do  prepared  statements which is similar  but different   Here is an example   mysql gt  PREPARE stmt FROM      - gt   select count         - gt  from information schema schemata      - gt  where schema name     or schema name        Query OK  0 rows affected  0 00 sec  Statement prepared mysql gt  EXECUTE stmt      - gt  USING  schema1  schema2  ----------    count       ----------           2    ----------  1 row in set  0 00 sec  mysql gt  DEALLOCATE PREPARE stmt    The prepared statements are often used to see an execution plan for a given query   Since they are executed with the execute command and the sql can be assigned to a variable you can approximate the some of the same behavior as dynamic sql   Here is a good link about this   Don t forget to deallocate the stmt using the last line   Good Luck

[mysql] How To have Dynamic SQL in MySQL Stored Procedure

Examples related to mysql

Examples related to dynamic