使用动态 SQL 通过向简单 sql 查询添加变量来指定列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7123659/
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
Using dynamic SQL to specify a column name by adding a variable to simple sql query
提问by goofyui
sql 2005/ sql 2008
sql 2005/ sql 2008
Declare @temp nvarchar(1000)
set @temp = 'ABC'
select col1,col2 from tableA
Along with select query, how to add a variable to the select query ?
与选择查询一起，如何向选择查询添加变量？
expected output :-
select col1,col2,@temp as [col3] from tableA
Where @temp specifies the name of a column in tableA.
其中@temp 指定表A 中的列名。
回答by JohnD
If you are trying to specify the column name dynamically, you could take a look at executing dynamic sql. However, you should make sure to read about the dangers of this approach first:
如果您尝试动态指定列名，则可以查看执行动态 sql。但是，您应该确保首先阅读这种方法的危险：
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dynamic_sql.html
From that page, there is a sample that shows dynamically specifying the table name -- you could change it so it dynamically specifies the column name instead:
在该页面上，有一个示例显示动态指定表名——您可以更改它，以便它动态指定列名：
CREATE PROCEDURE general_select @tblname nvarchar(128),
@key varchar(10),
@debug bit = 0 AS
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT col1, col2, col3
FROM dbo.' + quotename(@tblname) + '
WHERE keycol = @key'
IF @debug = 1 PRINT @sql
EXEC sp_executesql @sql, N'@key varchar(10)', @key = @key
So for example if you had a table 'MyTable' with columns named 'x', 'y', and 'z', it might look like:
因此，例如，如果您有一个名为“x”、“y”和“z”的列的表“MyTable”，它可能如下所示：
DECLARE @columnName nvarchar(128)
DECLARE @sql nvarchar(4000)
set @columnName = 'z'
SET @sql = 'SELECT x, y, ' + @columnName + ' from MyTable'
EXEC sp_executesql @sql, N'@columnName varchar(128)', @columnName = @columnName
回答by Chains
Something like this:
像这样的东西：
select col1,col2 from tableA WHERE col1 = @temp
Or this:
或这个：
select col1,col2,@temp as col3 from tableA WHERE col1 = @temp
Or this:
或这个：
select col1,col2,@temp as col3 from tableA
Or if @temp is a column name, then maybe you're looking for a dynamic query?
或者，如果@temp 是列名，那么您可能正在寻找动态查询？
SET @temp = 'select col1,col2, ' + @temp + ' as col3 from tableA'
EXEC sp_executesql @temp
...
...
