[SOLVED] How to set table name in dynamic SQL query?

How to set table name in dynamic SQL query?

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.

Solution

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.