Must declare the variable error

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)

Must declare the variable error

Author

Topic

mart0369
Starting Member

11 Posts

Posted - 2008-11-10 : 14:00:02

Answer found


declare @aTables table(
	name varchar(30),
	reg int,
	used int
)
declare @tablename varchar(30), @rID int, @used int

while exists(select * from @awardTables where used = 0)
  begin
	select @tablename = name, @used = used from @aTables where used = 0 and reg = @rID
		
	exec ('select * from @tablename')

	update @awardTables set used = 1 where region = @regionID
	set @regionID = @regionID + 2
   end		
go

When I try to use the @tablename to call exec statement, I get
Must declare the variable '@tablename'.

I found the answer. I needed to call the exec like this:


exec ('select * from' + @tablename)

Cheers

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-11-10 : 22:01:19

Table variables are out of scope within dynamic SQL. what exactly are you trying to do?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 01:31:54

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 01:52:11

why are you passing table name through variable?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources