CODEWITHSUNDEEP
sqlsql-serverstored-proceduresdynamic-sql
Erick Asto Oblitas
Erick Asto Oblitas

Reputation: 1409

Why I can't create a table with this dynamic SQL?

I have a procedure:

ALTER PROCEDURE SP_CREATETABLE
(
    @newTableName NVARCHAR(200)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX)
    SELECT @sql = 'create table @tableName (id int identity(1,1) primary key )'
    PRINT @sql
    EXEC sp_executesql @sql, N'@tableName nvarchar(200)', @newTableName
END

When I try to execute this:

EXEC SP_CREATETABLE 'NEWTABLENAME';

I get this error:

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '@tableName'.

Upvotes: 2

Views: 440

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

The reason you cannot create the table is because parameters only work for values in the query, not for table names or column names.

I often approach this using REPLACE():

ALTER PROCEDURE SP_CREATETABLE (
    @newTableName NVARCHAR(200)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SELECT @sql = 'create table @tableName (id int identity(1,1) primary key )';
    SELECT @sql = REPLACE(@sql, '@tableName', @newTableName);
    PRINT @sql;
    EXEC sp_executesql @sql;
END;

Upvotes: 1

Amit Sukralia
Amit Sukralia

Reputation: 950

You will need something like this:

ALTER PROCEDURE SP_CREATETABLE
(
    @newTableName NVARCHAR(200)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX)
    SELECT @sql = 'create table ' + QUOTENAME(@newTableName) + ' (id int identity(1,1) primary key )'
    PRINT @sql
    EXEC sp_executesql @sql
END

EXEC SP_CREATETABLE 'NEWTABLENAME';

Upvotes: 4

Related Questions