How to set table name in dynamic SQL query?

Question

I want to set table name in a dynamic SQL query. I tried successfully for parameter as following:

/* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

Now I want to take TABLE NAME dynamically using a parameter but I've failed to do that. Please guide me.

Pneumato

You can't parameterise the table name. You just do it manually in the SET @SQLQuery step. – Forde

@Forde actually you can pas the table name using it as a string and using exec of the string name(it behave exactly like a procedure) – Robinetta

@hellbaby - And still you can't supply the table name as a parameter to sp_executesql, this can only be accomplished with a substitution into the string. These are VERY different. Parameterisation allows type checking, protection from SQL Injection Attacks, execution plan reuse, etc. Substituting strings into other strings does none of that and so is not parameterisation. – Forde

@Forde actually you don't need the 'sp_executesql' command, in mssql 2008+ it's enough just to call in this way: exec @ myqueryconcatenatedvariablewithtablename and you got your answer; regarding injections - maybe you're right(someone with skills can break it). So depends where he need to use this after all... – Robinetta

@Robinetta - That's exactly the same mechanism - You're just substituting the table name into your string and then executing the resulting string. At no point in your example is the table name a parameter. – Forde

Answer 1

Table names cannot be supplied as parameters, so you'll have to construct the SQL string manually like this:

SET @SQLQuery = 'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = @EmpID' 

However, make sure that your application does not allow a user to directly enter the value of @TableName, as this would make your query susceptible to SQL injection. For one possible solution to this, see this answer.

Earthshaking

thanks but anyways i really dont want this one. can't pass as table name to sp_executesql :( – Pneumato

@Pneumato You should be able to use sp_executesql since you should already have the the correct table name inserted when setting your SQL string. This is a primary reason why SQL strings are constructed manually in the first place. – Bennett

Another consideration is to also apply QuoteName around the variable. That will properly wrap the table name with object wrappers, defaults to square brackets [...] – Percival

Answer 2

To help guard against SQL injection, I normally try to use functions wherever possible. In this case, you could do:

...
SET @TableName = '<[db].><[schema].>tblEmployees'
SET @TableID   = OBJECT_ID(TableName) --won't resolve if malformed/injected.
...
SET @SQLQuery = 'SELECT * FROM ' + QUOTENAME(OBJECT_NAME(@TableID)) + ' WHERE EmployeeID = @EmpID' 

Ld

Tip: The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space or reserved words like From. – Dumont

Answer 3

Try this:

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @TableName AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
SET @TableName = 'tblEmployees'
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

Cabrilla

definitely the complete answer I was looking for. – Softa

Brilliant! Solved my problem perfectly. – Actual

Open to SQL injection. All you need to do is pass a bad @TableName parameter such as 'sys.databaeses; Alter Server Role sysadmin Add Member [BadGuy]; --' and there goes your system. – Ember

Answer 4

This is the best way to get a schema dynamically and add it to the different tables within a database in order to get other information dynamically

select @sql = 'insert #tables SELECT ''[''+SCHEMA_NAME(schema_id)+''.''+name+'']'' AS SchemaTable FROM sys.tables'

exec (@sql)

of course #tables is a dynamic table in the stored procedure

Skysail

Answer 5

Building on a previous answer by @user1172173 that addressed SQL Injection vulnerabilities, see below:

CREATE PROCEDURE [dbo].[spQ_SomeColumnByCustomerId](
@CustomerId int,
@SchemaName varchar(20),
@TableName nvarchar(200)) AS
SET Nocount ON
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @Table_ObjectId int;
DECLARE @Schema_ObjectId int;
DECLARE @Schema_Table_SecuredFromSqlInjection NVARCHAR(125)

SET @Table_ObjectId = OBJECT_ID(@TableName)
SET @Schema_ObjectId = SCHEMA_ID(@SchemaName)
SET @Schema_Table_SecuredFromSqlInjection = SCHEMA_NAME(@Schema_ObjectId) + '.' + OBJECT_NAME(@Table_ObjectId)

SET @SQLQuery = N'SELECT TOP 1 ' + @Schema_Table_SecuredFromSqlInjection + '.SomeColumn 
FROM dbo.Customer 
INNER JOIN ' + @Schema_Table_SecuredFromSqlInjection + ' 
ON dbo.Customer.Customerid = ' + @Schema_Table_SecuredFromSqlInjection + '.CustomerId 
WHERE dbo.Customer.CustomerID = @CustomerIdParam 
ORDER BY ' + @Schema_Table_SecuredFromSqlInjection + '.SomeColumn DESC' 
SET @ParameterDefinition =  N'@CustomerIdParam INT'

EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @CustomerIdParam = @CustomerId; RETURN

Riess