Neo Neo - 1 year ago 128
SQL Question

how to set table name in dynamic sql query?

I want to set table name in 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 @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 parameter I'm failed to do that

please guide me.

Dan Dan
Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download