如何在动态 SQL 查询中设置表名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20678725/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set table name in dynamic SQL query?
提问by Neo
I want to set table name in a dynamic SQL query. I tried successfully for parameter as following:
我想在动态 SQL 查询中设置表名。我成功地尝试了如下参数：
/* 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 NAMEdynamically using a parameter but I've failed to do that. Please guide me.
现在我想TABLE NAME使用参数动态获取，但我没有这样做。请指导我。
回答by user1172173
To help guard against SQL injection, I normally try to use functions wherever possible. In this case, you could do:
为了防止 SQL 注入，我通常尽可能地尝试使用函数。在这种情况下，你可以这样做：
...
SET @TableName = '<[db].><[schema].>tblEmployees'
SET @TableID = OBJECT_ID(TableName) --won't resolve if malformed/injected.
...
SET @SQLQuery = 'SELECT * FROM ' + OBJECT_NAME(@TableID) + ' WHERE EmployeeID = @EmpID'
回答by Dan
Table names cannot be supplied as parameters, so you'll have to construct the SQL string manually like this:
表名不能作为参数提供，因此您必须像这样手动构造 SQL 字符串：
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.
但是，请确保您的应用程序不允许用户直接输入 的值@TableName，因为这会使您的查询容易受到 SQL 注入的影响。有关此问题的一种可能解决方案，请参阅此答案。
回答by Saharsh Shah
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
回答by CA Martin
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'
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
当然#tables是存储过程中的动态表
回答by outofcoolnames
Building on a previous answer by @user1172173 that addressed SQL Injection vulnerabilities, see below:
基于@user1172173 先前解决 SQL 注入漏洞的答案，请参见下文：
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
