I'm trying to create a table with the name of a parameter given to store procedure:
CREATE PROCEDURE INTENTO3
@table varchar(50)
AS
BEGIN
create table @table
(
campo1 varchar(20)
)
END
I always get the error :
Msg 102, Level 15, State 1, Procedure INTENTO3, Line 7 [Batch Start Line 1] Incorrect syntax near '@table'
In mySQL the name of the table can not be passed to it as a parameter. You need to use Dynamic SQL.
DELIMITER $$
CREATE DEFINER='root'@'localhost'
PROCEDURE 'create_tb_one'(In tableName varchar(255))
BEGIN
SELECT COUNT(1) INTO @table_exists
FROM information_schema.tables
WHERE table_schema=DATABASE()
AND table_name=tableName;
IF @table_exists = 0 THEN
SET @sql = CONCAT('CREATE TABLE ',tableName,' ');
SET @sql = CONCAT(@sql,'(ID INT NOT NULL,SNAME VARCHAR(100) NOT NULL,');
SET @sql = CONCAT(@sql,'SSTATE VARCHAR(100) NOT NULL,');
SET @sql = CONCAT(@sql,'TIME_STAMP VARCHAR(45) NOT NULL,');
SET @sql = CONCAT(@sql,'constraint FK_SENSOR ');
SET @sql = CONCAT(@sql,'foreign key (SNAME) ');
SET @sql = CONCAT(@sql,'references sensorInfo(SName) on delete set null)');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
END IF;
END $$
DELIMITER ;
In SQL Server I would do something like this:
CREATE PROCEDURE [dbo].[usp_Crear_Tabla]
-- Add the parameters for the stored procedure here
@nombreTabla varchar(50)
AS
BEGIN
Execute ('Create TABLE '+@nombreTabla+'(id int, nombre varchar(50))')
END
I hope you find it useful
you can try something like this: (mysql)
delimiter |
CREATE PROCEDURE SP_CREATE_TABLE_TEST ()
BEGIN
CREATE TABLE TEST
(
TestID int(11) default NULL,
TestName varchar(100) default NULL
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
END;
|