Table Name Placeholder - Tsql

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

Solution 1:

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'@Searchvarchar(10) ='Zohar';

IF EXISTS(
    SELECT1FROM Information_Schema.Columns
    WHERE TABLE_NAME =@TableNameAND COLUMN_NAME =@ColumnName
)
BEGINDECLARE@Sql nvarchar(4000) ='SELECT * FROM +' QUOTENAME(@TableName) +' WHERE '+ QUOTENAME(@ColumnName) +' LIKE ''%''+ @Search +''%'';'EXEC sp_executesql @Sql, N'@Search varchar(10)', @SearchEND-- 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)

Solution 2:

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;