Joining on table value in database

Question

I have a table that holds a relationship of the id to the actual name of the joining table, I need to be able to get the actual table name out with an ID value. example:

TableID    TableName
1          Test.Customers
2          Test.Orders
3          Test.Addresses

So to be able to pass in the ID of 1 and come back with "Customers" and to be able to use that to "SELECT * FROM Customers". What's the best approach to do this? What would that stored procedure look like?

Answer 1

You need to use Dynamic SQL, Like:

CREATE PROCEDURE dbo.MyProc (@ID int)
AS

DECLARE @SQL Varchar(1000) = ''

SELECT @SQL = 'SELECT * FROM ' + QUOTENAME(TableName)
FROM MyLookupTable
WHERE TableID = @ID

EXEC (@SQL)

Be very very very careful with Dynamic SQL, and read this before proceeding.