getting the table name from another table...

[e106199]

hi all,
i have a table that keeps some table names. Depending on the given ID i go to this table get the table name and want to run a select statement over this table. Here is what i did so far(this is running in a stored procedure):
.......
DECLARE @TABLENAME VARCHAR(50)
set @TABLENAME = (SELECT TABLENAME FROM typeTable WHERE TYPEID = @p1) --now the @tablename is the name of the table i want

select myCODE from anotherTable
where ID = (SELECT ID2 FROM @TABLENAME WHERE ID = @p1)
.........
here i m getting a "Must declare variable @TABLENAME". Its declared i think so i dont know what am i doing wrong here.
Any ideas?
thanks in advance
-shane

 

[SQLDenis]

Something like this (untested of course, but it should work)

DECLARE @TABLENAME VARCHAR(50)
set @TABLENAME = (SELECT TABLENAME FROM typeTable WHERE TYPEID = @p1) --now the @tablename is the name of the table i want


declare @SQL varchar(500)
create table #temp (id int)

select @SQL ='insert into #temp SELECT ID2 FROM ' + @TABLENAME +' WHERE ID = ' + convert(varchar,@p1) + ')'
exec (@SQL) --print to see statement --print @SQL

select myCODE from anotherTable a join #temp t on a.ID = t.id

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/