How to use a Table Variable

Hi all,

I have a table which holds a list of users names & the table assigned to each, e.g.

- John Smith - Table1
- bob Jones - Table2
etc

I have a store procedure which calls the information from the user's table based on the username. Something like this:

Select *
from @.UserTable

So if user is John Smith then @.UserTable = Table1.

Though the @. function does not work for referencing a table. Is there a workaround to this ?

Rgds & thanks.

A think that the only way is using an exec ou sp_executesql like this:

Declare @.command varchar(1000)

Select @.Command = 'Select * From ' + @.UserTable

Exec(@.Command) or sp_executesql @.Command

Regards.

|||Thats worked spot on .. thanks m8 |||How can I take the data from the executed table and put it into a temp table?|||Hi,

Couldn't you federate the username table and the multiple user tables into only one table and

use a stored procedure? That would save you a lot of trouble.

i.e. where you have:

Table_users

User_Name, value_1, Value_2, Value_3
Value 1 to n comes from the multiple user tables

then
Create Procedure up_Users @.User (Varchar(85))

Create table #TUser (Value_1 varchar(320), Value_2 varchar(320), value_3 varchar(320))

Insert Into #TUser(Value_1, Value_2, Value_3)

Select

value_1, Value_2, Value_3

from Table_Users u

Where u.User_Name = @.User

select * from #Tuser

drop table #Tuser

Philippe