Using the EXECUTE IMMEDIATE statement in procedures

The EXECUTE IMMEDIATE statement allows statements to be built up inside procedures using a combination of literal strings (in quotes) and variables.

For example, the following procedure includes an EXECUTE IMMEDIATE statement that creates a table.

CREATE PROCEDURE CreateTableProc(
		IN tablename char(30) )
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE ' || tablename ||' (column1 INT PRIMARY KEY)'
END

In ATOMIC compound statements, you cannot use an EXECUTE IMMEDIATE statement that causes a COMMIT, as COMMITs are not allowed in that context.