如何在动态SQL查询中设置表名? [英] How to set table name in dynamic SQL query?
问题描述
我想在动态SQL查询中设置表名.我成功尝试了如下参数:
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
现在,我想使用参数动态获取TABLE NAME
,但是我做不到.请引导我.
Now I want to take TABLE NAME
dynamically using a parameter but I've failed to do that. Please guide me.
推荐答案
表名不能作为参数提供,因此您必须像这样手动构建SQL字符串:
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'
但是,请确保您的应用程序不允许用户直接输入@TableName
的值,因为这会使您的查询容易受到SQL注入的影响.有关此问题的一种可能的解决方案,请参见此答案.
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.
这篇关于如何在动态SQL查询中设置表名?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!