使用动态 SQL 通过向简单 sql 查询添加变量来指定列名 [英] Using dynamic SQL to specify a column name by adding a variable to simple sql query
问题描述
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
其中@temp 指定表A 中列的名称.
Where @temp specifies the name of a column in tableA.
推荐答案
如果你想动态指定列名,你可以看看执行动态 sql.但是,您应该确保首先阅读这种方法的危险性:
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:
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
例如,如果您有一个包含名为x"、y"和z"的列的表MyTable",它可能如下所示:
So for example if you had a table 'MyTable' with columns named 'x', 'y', and 'z', it might look like:
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
这篇关于使用动态 SQL 通过向简单 sql 查询添加变量来指定列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!