SQL table selection

Question

I am trying to create some ways to dynamically select a table to start a procedure based on an identifier sent to the database. Sort of:

@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 perform dynamic queries. Read the Curse and blessings of dynamic SQL , it helped me a lot.