Invalid object name @tablename

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

Transact-SQL (2000)

Invalid object name @tablename

Author

Topic

omicron777
Starting Member

9 Posts

Posted - 2008-11-26 : 12:12:04

Here's my sql code that shows an error when executed in analyzer

Server: Msg 208, Level 16, State 1, Line 3
Invalid object name '@tablename'.

= = =
declare @tablename nvarchar(50)
set @tablename = 'menov2008'
select showbranch as branch,
sum(wh_stock) as wh,
sum(wh_stock * avg_cost) as whcost
into ME_Items
from [@tablename]
where substring(cardno,1,4) != 'WORK' and cardno
in (Select cardno from fg_imported.dbo.cardmaster_extension where mc
in ('GR','OB'))
group by showbranch
order by showbranch
GO
= = =

Any help pls!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 12:20:32

you need to use dynamic sql here


declare @tablename nvarchar(50),@sql varchar(8000)
set @tablename = 'menov2008'
set @sql='select showbranch as branch,
sum(wh_stock) as wh,
sum(wh_stock * avg_cost) as whcost
into ME_Items
from ['+ @tablename+']
where substring(cardno,1,4) != 'WORK' and cardno
in (Select cardno from fg_imported.dbo.cardmaster_extension where mc
in ('GR','OB'))
group by showbranch
order by showbranch'

EXEC (@sql)
GO

not sure why you want to send table name through a variable though

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-11-26 : 12:25:30

This requires dynamic SQL. Something like the following:

declare @tablename nvarchar(50),
@sql nvarchar(2000)
set @tablename = 'menov2008'

set @sql = 'select showbranch as branch,
sum(wh_stock) as wh,
sum(wh_stock * avg_cost) as whcost
into ME_Items
from [' + @tablename + ']
where substring(cardno,1,4) != ''WORK'' and cardno
in (Select cardno from fg_imported.dbo.cardmaster_extension where mc
in (''GR'',''OB''))
group by showbranch
order by showbranch'

print @SQL - This will show you dynamic string and point to potential issues
exec sp_executesql @SQL - obviously executes it

Edit - Sorry but by time I pulled it together and copy/pasted into the reply, visakh already replied. The guy is fast!!! Visakh - Do you ever sleep??

Terry

omicron777
Starting Member

9 Posts

Posted - 2008-11-26 : 12:35:19

Thanks guys! Prob solved.

I'm a newbie in sql server, so I'm a little mixed up.

omicron777
Starting Member

9 Posts

Posted - 2008-11-26 : 12:39:18

By the way, I'm using C# and throwing the tablename variable to sp.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 03:13:50

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail