Table name placeholder - TSQL

Question

Is there a way to do something like this? People is the name of the table.

declare @placeholder varchar(20) = 'People'
select * from @placeholder

Or something like this where the table name is People_Backup.

declare @placeholder varchar(20) = '_Backup'
select * from People@placeholder

And is there a way to add in dynamic sql the value of a variable? something like this:

declare @placeholder nvarchar(20) = 'people'
declare @name nvarchar(30) = 'antony'

declare @query nvarchar(1000) = 'select * from ' + @placeholder + ' where 
first_name=' + @name 
exec sp_executesql @query

I mean: without do this

exec sp_executesql @query, N'@name varchar(30)', @name

Thank you for the answers.

score 6 · Answer 1 · edited Jun 20 '20 at 09:12

Not without dynamic SQL.
Parameters in SQL are placeholders for data, and can't be used as placeholders for anything else (which includes commands such as select, update etc' and identifiers such as database name, schema name, table name, column name etc').

The only way to parameterize table names is to use dynamic SQL - meaning you must build a string containing the SQL you want to execute, and then execute it.
Beware - dynamic SQL might be an open door for SQL injection attacks - so you must do it wisely - here are some ground rules:

Always white-list your identifiers (using system tables or views such as sys.Tables or Information_schema.Columns)
Always use sysname as the datatype for identifiers.

The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers. Therefore, it can vary between instances of SQL Server.
Never pass SQL commands or clauses in parameters - set @placeholder = 'select a, b, c' or set @placeholder = 'where x = y' is a security hazard!
Always use parameters for data. Never concatenate parameters into your sql string: set @sql = 'select * from table where x = '+ @x is a security hazard. Always create your dynamic SQL to use parameters as parameters: set @sql = 'select * from table where x = @x'
Always use sp_executeSql to execute your dynamic SQL statement, not EXEC(@SQL).
For more information, read Kimberly Tripp's EXEC and sp_executesql – how are they different?
Always wrap identifiers with QUOTENAME() to ensure correct query even when identifiers include chars like white-spaces

To recap - a safe version of what you are asking for (with an additional dynamic where clause to illustrate the other points) is something like this:

@DECLARE @TableName sysname = 'People',
         @ColumnName sysname = 'FirstName'
         @Search varchar(10) = 'Zohar';

IF EXISTS(
    SELECT 1
    FROM Information_Schema.Columns
    WHERE TABLE_NAME = @TableName 
    AND COLUMN_NAME = @ColumnName
)
BEGIN
    DECLARE @Sql nvarchar(4000) = 
    'SELECT * FROM +' QUOTENAME(@TableName) +' WHERE '+ QUOTENAME(@ColumnName) +' LIKE ''%''+ @Search +''%'';'

    EXEC sp_executesql @Sql, N'@Search varchar(10)', @Search

END
-- you might want to raise an error if not

To answer your question after edited directly:

I mean: without do this exec sp_executesql @query, N'@name varchar(30)', @name

Yes, you can do it without using sp_executeSql, but it's dangerous - it will enable an attacker to use something like '';DROP TABLE People;-- as the value of @name, so that when you execute the sql, your People table will be dropped.

To do that, you will need to wrap the @name with ' -

declare @placeholder nvarchar(20) = 'people'
declare @name nvarchar(30) = 'antony'

declare @query nvarchar(1000) = 'select * from ' + QUOTENAME(@placeholder) + ' where 
first_name=''' + @name +''''
exec(@query)

And one more: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. — HABO, Oct 21 '18 at 14:35
@HABO Correct! I don't know why I didn't write it in the first place. Adding to the list — Zohar Peled, Oct 21 '18 at 15:18
A while ago I've written a post based on this answer in What the # do I know? called [The do’s and don’ts of dynamic SQL for SQL Server](https://zoharpeled.wordpress.com/2019/09/12/the-does-and-donts-of-dynamic-sql-for-sql-server-%ef%bb%bf/). It contains a bit more information. — Zohar Peled, Dec 02 '19 at 12:51

Ilyes · Accepted Answer · 2018-10-21T11:38:45.040

0

I mean: without do this exec sp_executesql @query, N'@name varchar(30)', @name

Yes, you can do that as

--Use MAX instead of 1000
DECLARE @SQL nvarchar(MAX) = N'SELECT * FROM ' + @placeholder + ' WHERE first_name = '''+@name +'''';
EXECUTE sp_executesql @SQL;

edited Oct 21 '18 at 11:38

answered Oct 21 '18 at 09:48

Ilyes

14,640
4
29
55

Table name placeholder - TSQL

2 Answers2

Linked