using parameter values in stored procedures

Question

I have the following SQL code in a stored procedure:

CREATE PROCEDURE sp_createAColDiffTable (@table_name nvarchar(128))
AS

CREATE TABLE @table_name ( 

[table_name]       VARCHAR (128) NOT NULL,
[column_name]      VARCHAR (128) NULL,
[what_changed]     VARCHAR (128) NULL,
[sr_data_type]     VARCHAR (128) NOT NULL,
[tr_data_type]     VARCHAR (128) NOT NULL,
[sr_max_length]    SMALLINT NOT NULL,
[tr_max_length]    SMALLINT NOT NULL,
[sr_is_nullable]   CHAR NULL,
[tr_is_nullable]   CHAR NULL,
[sr_precision]     SMALLINT NULL,
[tr_precision]     SMALLINT NULL,
[sr_scale]         SMALLINT NULL,
[tr_scale]         SMALLINT NULL )
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE)

I call the stored procedure like this:

EXEC [sp_createAColDiffTable] 'hq193.dbo.arch_con_col_s193_s202'

When I run the code I get the error:

Incorrect syntax near '@table_name'.

When I substitute the actual table name for @table_name it works fine. Can someone point out the problem with my syntax?

Probably a [duplicate](http://stackoverflow.com/questions/10877407/t-sql-how-to-create-tables-dynamically-in-stored-procedures) — Sergey Rybalkin, Oct 07 '13 at 12:17
@user2135970 A Small Suggession while creating Stored procedure **Don't** use `sp_` as prefix as the Microsoft useses it for reserved Stored Procedures in SSMS like `sp_tables`,`sp_columns` — Rajesh, Oct 07 '13 at 12:33

score 3 · Accepted Answer · answered Oct 07 '13 at 12:15

You would need to use dynamic SQL for this, as CREATE TABLE doesn't take a variable name.

e.g.

CREATE PROCEDURE sp_createAColDiffTable (@table_name nvarchar(128))
AS

DECLARE @sql nvarchar(MAX)

SET @sql = 'CREATE TABLE ' + @table_name + ' (    
[table_name]       VARCHAR (128) NOT NULL,
[column_name]      VARCHAR (128) NULL,
[what_changed]     VARCHAR (128) NULL,
[sr_data_type]     VARCHAR (128) NOT NULL,
[tr_data_type]     VARCHAR (128) NOT NULL,
[sr_max_length]    SMALLINT NOT NULL,
[tr_max_length]    SMALLINT NOT NULL,
[sr_is_nullable]   CHAR NULL,
[tr_is_nullable]   CHAR NULL,
[sr_precision]     SMALLINT NULL,
[tr_precision]     SMALLINT NULL,
[sr_scale]         SMALLINT NULL,
[tr_scale]         SMALLINT NULL )
ON [PRIMARY] WITH (DATA_COMPRESSION = NONE)';
EXEC(@sql);

Be aware that using dynamic SQL can lead to SQL injection though, so make sure you never pass any user input to @table_name.

+1 - this is correct. Without dynamic SQL, you may only use variables in place of values - not object names. Be wary of SQL injection attacks when using dynamic SQL, though! — Dan, Oct 07 '13 at 12:16
@Dan - good shout. Was just editing my answer to include that. — Matt Whitfield, Oct 07 '13 at 12:18

score 1 · Answer 2 · edited Oct 08 '13 at 09:44

@table_name is used when declaring table variables, whereas table_name or #table_name or ##table_name is used to create tables/temp tables.

So you would have

DECLARE @table TABLE (
    ID INT,
    ....

and

CREATE TABLE #table_name (
    ID INT,
    ....

If you wish to create a table dynamically, you would have to use dynamic sql for that.

For declaring a table variable have a look at DECLARE @local_variable (Transact-SQL)

For creating tables/temp tables have a look at CREATE TABLE (Transact-SQL)

Further to that, also have a look at The Curse and Blessings of Dynamic SQL

score 0 · Answer 3 · edited Oct 07 '13 at 13:47

0

Maybe try CREATE TABLE [dbo].[' + @table_name + '].

edited Oct 07 '13 at 13:47

Liam

27,717
28
128
190

answered Oct 07 '13 at 12:16

Iwona Kubowicz

364
3
15

score 0 · Answer 4 · answered Oct 07 '13 at 12:24

0

You should use sp_executesql, see http://technet.microsoft.com/en-us/library/ms188001.aspx

answered Oct 07 '13 at 12:24

George Mavritsakis

6,829
2
35
42

score 0 · Answer 5 · answered Oct 07 '13 at 12:28

You need to write here a dynamic query like this..
declare  @table_name  nvarchar(200)='table1'
declare @query nvarchar(4000)='
create table '+ @table_name +'(
[table_name]       VARCHAR (128) NOT NULL,
[column_name]      VARCHAR (128) NULL,
[what_changed]     VARCHAR (128) NULL,
[sr_data_type]     VARCHAR (128) NOT NULL,
[tr_data_type]     VARCHAR (128) NOT NULL,
[sr_max_length]    SMALLINT NOT NULL,
[tr_max_length]    SMALLINT NOT NULL,
[sr_is_nullable]   CHAR NULL,
[tr_is_nullable]   CHAR NULL,
[sr_precision]     SMALLINT NULL,
[tr_precision]     SMALLINT NULL,
[sr_scale]         SMALLINT NULL,
[tr_scale]         SMALLINT NULL )'
EXEC sp_executesql @query

exec('select * from '+@table_name)

score 0 · Answer 6 · answered Oct 07 '13 at 12:30

you could always just use the rename stored procedure for example

CREATE PROCEDURE sp_createAColDiffTable 
(
    @table_name nvarchar(128)
 )
AS

CREATE TABLE TempTable
( 

    [table_name]       VARCHAR (128) NOT NULL,
    [column_name]      VARCHAR (128) NULL,
    [what_changed]     VARCHAR (128) NULL,
    [sr_data_type]     VARCHAR (128) NOT NULL,
    [tr_data_type]     VARCHAR (128) NOT NULL,
    [sr_max_length]    SMALLINT NOT NULL,
    [tr_max_length]    SMALLINT NOT NULL,
    [sr_is_nullable]   CHAR NULL,
    [tr_is_nullable]   CHAR NULL,
    [sr_precision]     SMALLINT NULL,
    [tr_precision]     SMALLINT NULL,
    [sr_scale]         SMALLINT NULL,
    [tr_scale]         SMALLINT NULL 
)

EXEC sp_rename 'TempTable', @table_name

score 0 · Answer 7 · answered Mar 12 '19 at 22:54

i used the CREATE TABLE tblVarName LIKE staticname;. i have static tables definitions and the CREATE TABLE LIKE allows to pass a string variable and it create the table with the same definitions except foreign keys constraint .so, after create i do the ALTER TABLE ADD CONSTRAINS

using parameter values in stored procedures

7 Answers7