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?

@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
– Rajesh, Commented Oct 7, 2013 at 12:33

Matt Whitfield · Accepted Answer · 2013-10-07 12:15:08Z

3

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.

answered Oct 7, 2013 at 12:15

Matt Whitfield

6,6343 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Over a year ago

+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!

Matt Whitfield Over a year ago

@Dan - good shout. Was just editing my answer to include that.

Matt Whitfield · Accepted Answer · 2013-10-08 09:44:38Z

1

@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

edited Oct 8, 2013 at 9:44

Matt Whitfield

6,6343 gold badges31 silver badges47 bronze badges

answered Oct 7, 2013 at 12:15

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Comments

George Mavritsakis · Accepted Answer · 2013-10-07 12:24:09Z

0

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

answered Oct 7, 2013 at 12:24

George Mavritsakis

7,1332 gold badges37 silver badges44 bronze badges

Comments

Dilip Kr Singh · Accepted Answer · 2013-10-07 12:28:45Z

0

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)

answered Oct 7, 2013 at 12:28

Dilip Kr Singh

1,6202 gold badges19 silver badges29 bronze badges

Comments

Squirrel5853 · Accepted Answer · 2013-10-07 12:30:30Z

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

Liam · Accepted Answer · 2013-10-07 13:47:46Z

0

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

edited Oct 7, 2013 at 13:47

Liam

30k28 gold badges143 silver badges206 bronze badges

answered Oct 7, 2013 at 12:16

Iwona Kubowicz

3743 silver badges15 bronze badges

Comments

meir · Accepted Answer · 2019-03-12 22:54:50Z

0

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

answered Mar 12, 2019 at 22:54

meir

1

Collectives™ on Stack Overflow

using parameter values in stored procedures

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related