CODEWITHSUNDEEP
mysqlsqlstored-procedures
nobrandheroes
nobrandheroes

Reputation: 758

Passing arguments in Stored Procedure

As apart of a larger task, I am trying to create a MySQL stored procedure that creates a table. I would like to pass the table's name in the procedure call.

CREATE PROCEDURE SP_CREATE_TABLE(IN tableName VARCHAR(100))
    SET @table_name = tableName;
    SET @query = CONCAT('CREATE TABLE IF NOT EXISTS meta_', @table_name, '(`created` DATETIME, `modified` DATETIME, `enabled` TINYINT(1) DEFAULT 1);');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

and then there is the call:

CALL SP_CREATE_TABLE('data');

The procedure works if I hard code `@table_name, but refuses to take the passed parameters.

I am on MySQL 6. Any input?

Upvotes: 0

Views: 75

Answers (1)

Arun
Arun

Reputation: 951

Try this:

DELIMITER $$
 CREATE PROCEDURE SP_CREATE_TABLE(IN tableName VARCHAR(100))
 BEGIN
  SET @table_name = tableName;
  SET @query = CONCAT('CREATE TABLE IF NOT EXISTS meta_', @table_name, '(`created` DATETIME, `modified` DATETIME, `enabled` TINYINT(1) DEFAULT 1);');
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
 END $$
DELIMITER ;

Upvotes: 1

Related Questions