Is it possible to write a query where table name is retrieved from cell in another table?-CodePudding

I have a table dbo.TableNames with a varchar column TableName containing table names:

    |    Id     | TableName             |
     ----------- ----------------------- 
    |    1      | dbo.BookDictionary    |     
    |    2      | dbo.AnotherDictionary |

What I am curious about, is there a way to instead of writing:

SELECT * 
FROM dbo.BookDictionary

write something like:

SELECT * 
FROM 
    (SELECT TableName FROM dbo.TableNames WHERE Id = 1)

CodePudding user response：

It is possible but i wouldnt recommend it, because it will result in performance drops quit fast

declare @sql varchar(max)

SELECT @sql = CONCAT('SELECT * FROM ', [TableName], ';') 
FROM [dbo].[TableNames] 
WHERE Id = 1

sp_executesql @sql

CodePudding user response：

If there is any chance that your TableNames values could be controlled by the end-user, you'll want to carefully verify the value retrieved to avoid a potential SQL Injection attack.

DECLARE @TableName nvarchar(max);
SELECT @TableName = TableName FROM dbo.TableNames WHERE ID = @ID;
If @TableName Is Null RAISERROR('Table %d does not exist.', 16, 1, @ID);

DECLARE @VerifiedSchemaName sysname, @VerifiedTableName sysname;

SELECT
    @VerifiedSchemaName = S.name,
    @VerifiedTableName = T.name
FROM
    sys.tables As T
    INNER JOIN sys.schemas As S
    ON S.schema_id = T.schema_id
WHERE
    @TableName = S.name   N'.'   T.name
;

If @@ROWCOUNT = 0 RAISERROR('Table "%s" does not exist.', 16, 1, @TableName);

DECLARE @sql nvarchar(max);
SET @sql = CONCAT('SELECT * FROM ', 
    QUOTENAME(@VerifiedSchemaName), 
    N'.', 
    QUOTENAME(@VerifiedTableName), 
    N';');

EXEC(@sql);