SQL Table selection

Question

I'm trying to create some means of dynamically selecting the table for a procedure to run on based on an ID sent to the database. Something like :

@TableId int
As

Declare @nameoftable varchar(50)

select @nameoftable = Nameoftable from tablelist where id = @tableid


-- returning on selected table

Select somestuff
from @nameoftable

Any ideas?

Answer 1

You need to use dynamic SQL

@TableId int
As

Declare @nameoftable varchar(50)
select @nameoftable = Nameoftable from tablelist where id = @tableid

-- returning on selected table

declare @sql nvarchar(1000)
set @sql = 'Select somestuff from ' + Quotename(@nameoftable)
exec(@sql)

Answer 2

In MS SQL Server you can use sp_executesql to execute dynamic queries. Read The Curse and Blessings of Dynamic SQL, it helped me alot.