err in creating a table - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

err in creating a table

Author

Topic

senthilramtrs
Starting Member

29 Posts

Posted - 2008-05-14 : 04:56:42

while i m creating a table in stored procedure with the given input parameter .... it is showing error...

err on this line:

create table @in (ColName varchar(100))

y?

Regards,
Senthil Ram TRS

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-14 : 05:04:04

You can't use a variable to define a table naem like that. It you want to be able to specify the tble name as a variable (why?) you will need to use dynamic SQL

DECLARE @sSQL VARCHAR(500)
SET @sSQL = 'create table ' + @in + ' (ColName varchar(100))'
EXECUTE(@sSQL)

--
Gail Shaw

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-14 : 05:05:28

Because table name can not be dyanmic. If you want to do this as it is, use dynamic sql.

declare @sql varchar(7000)

set @sql = 'create table ' + @in + '(ColName varchar(100))'
exec(@sql)

Please read this before going for D-Sql: http://www.sommarskog.se/dynamic_sql.html

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 05:18:35

We don't know that the input parameter is for yet.
Maybe he just want to do

DECLARE @in (ColName varchar(100))

and then use the input parameter to populate the table variable?
With

insert @in
select * from mytable where col1 = @inputparameter

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 10:20:15

quote:
Originally posted by Peso

We don't know that the input parameter is for yet.
Maybe he just want to do

DECLARE @in table(ColName varchar(100))

and then use the input parameter to populate the table variable?
With

insert @in
select * from mytable where col1 = @inputparameter

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources