Use of variables in table names and fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Use of variables in table names and fields

Author

Topic

Steve_H
Starting Member

7 Posts

Posted - 2006-06-22 : 02:06:11

Hi, I am new to using SQL for anything more in depth than querying and reporting.

I am trying to create a series of SQL scripts which will be used across several customer sites so need to be easily customisable. What I want to do is have all of the table names, field names and customisable items handled by variables which will be declared and set at the beginning of the script, making them easy to find and change. The problem I am having at the moment is with creating a new table using variables for table name and field names, can anyone help?

quote:

DECLARE
@a_tmptbl varchar(15),
@a_fieldid1 varchar(15)

set @a_fieldid1 = 'newFieldid'
set @a_tmptbl = 'newTable'

create table @a_tmptbl ( @a_fieldid1 varchar(15), value float, counter INT);

insert into @a_tmptbl values ( "foobar", 21.76, 1);

select * from @a_tmptbl;

The error I am getting is:

quote:
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@a_tmptbl'.

Any advice would be gratefully accepted

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-22 : 02:11:39

If you want to do that, you will have to use Dynamic SQL.

DECLARE
@a_tmptbl varchar(15),
@a_fieldid1 varchar(15),
@sql nvarchar(4000)

set @a_fieldid1 = 'newFieldid'
set @a_tmptbl = 'newTable'

select @sql = 'create table ' + @a_tmptbl + '(' +  @a_fieldid1 + 'varchar(15), value float, counter INT)'
exec (@sql)

select @sql = 'insert into ' + @a_tmptbl + ' values ( "foobar", 21.76, 1)'
exec (@sql)

select @sql = 'select * from ' + @a_tmptbl
exec (@sql)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-22 : 03:35:28

It is not advisable to pass object names as parameters. Read more about Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Steve_H
Starting Member

7 Posts

Posted - 2006-06-22 : 20:17:58

Thanks guys, that is really helpful

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources