Create table by passing name as early defined and value assigned variable in SQL Server 2012

Question

I was trying to create stored procedure for create table as below:

CREATE PROCEDURE tableCreation
AS
    DECLARE @tableName NVARCHAR(30)
    SET @tableName = 'Employee'
    BEGIN

        CREATE TABLE @tableName (
            id INT NOT NULL
        )
    END

but when I execute the procedure, I get this error:

Msg 102, Level 15, State 1, Procedure tableCreation, Line 7
Incorrect syntax near '@tableName'.

What is the issue with above procedure or is there any other way to achieve above task?

You need dynamic SQL for this kind of thing. – Radu Gheorghiu Jan 27 '16 at 15:14 — Radu Gheorghiu, Jan 27 '16 at 15:14

Paul Hunt · Answer 1 · 2016-01-27T15:23:27.910

2

You should build a string containing the SQL you need and then execute that string. This is known as dynamic SQL.

create procedure tableCreation
as
   declare @tableName nvarchar(30)
begin

    declare @Sql NVARCHAR(MAX) = 'create table ' + @tableName + '
    (
        id int not null
    )'

    EXEC(@Sql)
end

You need to ensure that you validate the value in the @tableName variable before it's passed in to avoid SQL injection attacks

http://www.troyhunt.com/2013/07/everything-you-wanted-to-know-about-sql.html https://xkcd.com/327/

edited Jan 27 '16 at 15:23

answered Jan 27 '16 at 15:16

Paul Hunt

3,395
2
24
39

Thank you very much for your support – Azhar Azmie Jan 27 '16 at 15:30

Radu Gheorghiu · Answer 2 · 2016-01-27T15:23:13.797

1

This might be something that will help, for starters:

create procedure tableCreation
as
    declare @tableName nvarchar(30)
    set @tableName = 'Employee'
begin

    declare @sql nvarchar(max)

    set @sql = 'CREATE TABLE ' + @tableName + '( id int not null )';

    exec sp_executesql @sql

end

But there are better (but still not ideal) ways of using dynamic SQL, for example parametrizing your query which will decrease the changes of your code being prone to SQL Injection. Check out one of the established articles on this topic.

But, if you do not pass the table name as a parameter to the procedure, then I guess you're safe from SQL Injection (although this is some kind of obscure and strange behaviour).

edited Jan 27 '16 at 15:23

answered Jan 27 '16 at 15:17

Radu Gheorghiu

20,049
16
72
107

it is working fine,thank you for your support – Azhar Azmie Jan 27 '16 at 15:29
@AzharAzmie You're welcome. Don't forget to upvote/mark the correct answer as per Stackoverflow good spirit. – Radu Gheorghiu Jan 27 '16 at 15:30

score 0 · Answer 3 · answered Jan 27 '16 at 15:17

CREATE PROCEDURE tableCreation
(
    @tableName SYSNAME
)
AS
BEGIN

    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = '
        CREATE TABLE ' + @tableName + ' (
            id INT NOT NULL
        )'

    --PRINT @SQL
    EXEC sys.sp_executesql @SQL

END
GO

EXEC dbo.tableCreation @tableName = 'Employee'

Create table by passing name as early defined and value assigned variable in SQL Server 2012

3 Answers3