在 SQL 查询中动态选择列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8454671/
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
Dynamically choose column in SQL query
提问by k80sg
I have a database field name call Codeand I am trying to select it using a variable name like this below:
我有一个数据库字段名称调用Code，我正在尝试使用如下变量名称来选择它：
Declare @var1 = [Code]
(SELECT @var1
FROM [VoucherType]
WHERE [DeletedBy] IS NULL
AND [AutoID] = 1)
Apparently, SQL will interpret @var1as a string and not the field of my database, how can I do it in such a way @var1is recognized as the field name [Code]instead of a string possibly without any select or if statements.
显然，SQL 将解释@var1为字符串而不是我的数据库的字段，我怎么能这样做@var1被识别为字段名称[Code]而不是字符串，可能没有任何选择或 if 语句。
回答by Nonym
Try this:
尝试这个：
DECLARE @var1 VARCHAR(20)
DECLARE @sql VARCHAR(255)
SET @var1 = 'Code'
SET @sql = 'select ' + @var1 + ' from [VoucherType] where [DeletedBy] is null and [AutoID] = 1'
EXEC sp_executesql @sql
You'll have to compose a dynamic query, and execute using sp_executesql
您必须编写一个动态查询，并使用 sp_executesql
To add more on the 'dynamic' side of things, use stored procedures. See here for an example:
要在事物的“动态”方面添加更多内容，请使用存储过程。请参见此处的示例：
http://www.marten-online.com/database/execute-dynamic-sql-in-mssql.html
http://www.marten-online.com/database/execute-dynamic-sql-in-mssql.html
That is... if you are using Microsoft SQL SERVER
也就是说...如果您使用的是 Microsoft SQL SERVER
回答by BizApps
You need to use Dynamic SQL.
您需要使用动态 SQL。
declare @ColName varchar(128)
declare @sql varchar(4000)
Set @ColName='Code';
select @sql = 'SELECT '+@ColName+'
FROM [VoucherType]
WHERE [DeletedBy] IS NULL
AND [AutoID] = 1'
exec sp_executesql @sql
go
This post might be helpful
这篇文章可能会有所帮助
Accessing a table from a name in a variable
SQL: Select dynamic column name based on variable
Regards
问候
回答by Mitch Wheat
You cannot use a variable like that in a SELECT statement.
您不能在 SELECT 语句中使用这样的变量。
You will need to create dynamic TSQL.
您将需要创建动态 TSQL。
You don't specify your RDBMS, but in SQL Server you would use sp_executesql(preferably) or EXEC
您没有指定 RDBMS，但在 SQL Server 中您将使用sp_executesql（最好）或EXEC
Declare @var1 varchar(100)
Declare @sql varchar(1000)
SET @var1 = '[Code]'
SET @sql = ' select ' + @var1 + ' from [VoucherType]' +
' where [DeletedBy] is null and [AutoID] = 1'
EXEC sp_executesql @sql
Be sure to read: The Curse and Blessings of Dynamic SQL
请务必阅读：动态 SQL 的诅咒和祝福
回答by Elias Hossain
Please try with below code:
请尝试使用以下代码：
DECLARE @var1 VARCHAR(50)
声明 @var1 VARCHAR(50)
SET @var1 = '[Code]'
EXEC ('SELECT ' + @var1 + ' FROM [VoucherType]
WHERE [DeletedBy] IS NULL AND [AutoID] = 1 ')
