Joining on table value in database
I have a table that holds a relationship of the id to the actu开发者_StackOverflowal 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?
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.
精彩评论