blocks|key|1377739|text|您需要使用动态SQL：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1377740|DECLARE+@input+VARCHAR(10)+=+'+Person+';+

DECLARE+@sql+NVARCHAR(MAX)+=+'
SELECT+count(*)+AS++[@Input+Open+Data]++++
FROM+#Accounts+a+JOIN
+++++dbo.FileFeed+t
+++++On+t.ID+=+a.AccountID+++++
GROUP+BY+a.accountid';

SET+@sql+=+REPLACE(@sql,+'@Input',+@Input);

exec+sp_executesql+@sql;|code-block|syntax|javascript|1377741|然而，我并不认为这是一个好主意。如果需要重命名列，请在应用程序代码中这样做。|1377742|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You need to use dynamic SQL:

<pre><code>DECLARE @input VARCHAR(10) = ' Person '; 

DECLARE @sql NVARCHAR(MAX) = '
SELECT count(*) AS [@Input Open Data] 
FROM #Accounts a JOIN
 dbo.FileFeed t
 On t.ID = a.AccountID 
GROUP BY a.accountid';

SET @sql = REPLACE(@sql, '@Input', @Input);

exec sp_executesql @sql;
</code></pre>

However, I don't really think this is a good idea. If you need to rename a column, do it in the application code.

blocks|key|879565|text|在没有的情况下，使用临时表和重命名列是一种丑陋的方式：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|879566|DECLARE+@input+VARCHAR(10)+=+'+Person+';+
DECLARE+@new_name+VARCHAR(100)+=+@input+%2B+'+Open+Data';

SELECT+[rename_me]+=+COUNT(*)
INTO+#temp++++
FROM+#Accounts+a
JOIN+dbo.FileFeed+t+On+t.ID+=+a.AccountID+++++
GROUP+BY+a.accountid;

EXEC+tempdb..sp_rename+'#temp.rename_me',+@new_name,+'COLUMN';

SELECT+*+
FROM+#temp;|code-block|syntax|javascript|879567|entityMap^0|3|O|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

One ugly way, without <code>Dynamic-SQL</code> is using temporary table and rename column:

<pre><code>DECLARE @input VARCHAR(10) = ' Person '; 
DECLARE @new_name VARCHAR(100) = @input + ' Open Data';

SELECT [rename_me] = COUNT(*)
INTO #temp 
FROM #Accounts a
JOIN dbo.FileFeed t On t.ID = a.AccountID 
GROUP BY a.accountid;

EXEC tempdb..sp_rename '#temp.rename_me', @new_name, 'COLUMN';

SELECT * 
FROM #temp;
</code></pre>

blocks|key|1846354|text|不是的。数据库使用类似于编译的过程将查询转换为执行计划。此过程的一部分涉及确定运行查询的用户是否具有访问查询所使用的表和列的权限。如果这些表和列直到执行时才确定，则编译步骤无法完成。这似乎很奇怪，但同样的事情也适用于结果集。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1846355|动态SQL+(它创建一个新的查询，并在新的编译步骤中预先知道表和列名)将是实现这一目标的唯一方法。|1846356|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

No. Databases use a process similar to compiling to turn your query into an execution plan. Part of this process involves determining whether the user running the query has permissions to access the tables and columns used by the query. If those tables and columns are not determined until execution time, the compilation step can't finish. It seems strange, but the same thing applies to the result set.

Dynamic SQL (which creates a new query, with a new compilation step where the tables and column names are known up front) will be the only way to accomplish this.

blocks|key|879554|text|答案是“不”。根据定义，动态SQL意味着代码可以更改自身。除了动态SQL之外，唯一的替代方法是硬编码SQL，这是当您自己专门编写它的每一部分时。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|879555|您在询问是否可以根据变量更改字段名，这只能用动态SQL来完成。|879556|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

The answer is "No". Dynamic SQL, by definition, means that the code can alter itself. The only alternative to Dynamic SQL is Hard-Coded SQL, which is when you specifically write every piece of it yourself.

You're asking if you can change a field name based on a variable, which can only be done with Dynamic SQL.

blocks|key|1846363|text|除非您重命名应用程序中的列，否则我会这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1846364|DECLARE+@input+VARCHAR(10)='+Person+';+
Declare+@sql+varchar(max);
set+@sql'SELECT+count(*)+as+['%2B+@input+%2B'+Open+Data]
FROM+#Accounts+a
JOIN+dbo.FileFeed+t+On+t.ID+=+a.AccountID+++++
GROUP+BY+a.accountid';

EXEC(@sql);|code-block|syntax|javascript|1846365|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Unless you rename the columns in the application, this is the way i would do it.

<pre><code>DECLARE @input VARCHAR(10)=' Person '; 
Declare @sql varchar(max);
set @sql'SELECT count(*) as ['+ @input +' Open Data]
FROM #Accounts a
JOIN dbo.FileFeed t On t.ID = a.AccountID 
GROUP BY a.accountid';

EXEC(@sql);
</code></pre>

blocks|key|1377860|text|如果@input变量/参数中的值是有限的(即不是开放用户输入或类似性质的东西)，那么您可以不使用DynamicSQL来完成这一任务。您只需要在这个存储过程中拥有多个查询副本，除了列的别名之外，每个副本都是完全相同的，并根据变量的值选择您想要的查询副本。例如：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1377861|DECLARE+@input+VARCHAR(10)='+Person+';+

IF+(@input+=+'Person')
BEGIN
++SELECT+COUNT(*)+AS+[Person+Open+Data]
++FROM+#Accounts+a
++JOIN+dbo.FileFeed+t
++++ON+t.ID+=+a.AccountID+++++
++GROUP+BY+a.accountid;
END;

IF+(@input+=+'Account')
BEGIN
++SELECT+COUNT(*)+AS+[Account+Open+Data]
++FROM+#Accounts+a
++JOIN+dbo.FileFeed+t
++++ON+t.ID+=+a.AccountID+++++
++GROUP+BY+a.accountid;
END;

...|code-block|syntax|javascript|1377862|entityMap^0|2|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

IF it is the case that the values of what can be in the <code>@input</code> variable / parameter are limited (i.e. not open-ended user input or something of that nature), then you can do this without DynamicSQL. You just need to have multiple copies of the query in this Stored Procedure, each being exactly the same except for the alias of the column, and pick the one you want based on the value of the variable. For example:

<pre class="lang-sql prettyprint-override"><code>DECLARE @input VARCHAR(10)=' Person '; 

IF (@input = 'Person')
BEGIN
 SELECT COUNT(*) AS [Person Open Data]
 FROM #Accounts a
 JOIN dbo.FileFeed t
 ON t.ID = a.AccountID 
 GROUP BY a.accountid;
END;

IF (@input = 'Account')
BEGIN
 SELECT COUNT(*) AS [Account Open Data]
 FROM #Accounts a
 JOIN dbo.FileFeed t
 ON t.ID = a.AccountID 
 GROUP BY a.accountid;
END;

...
</code></pre>

blocks|key|1377871|text|如果您获得了Server的更高版本，那么您可能会看到以下选项：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1377872|EXEC+<stored_procedure>+WITH+RESULT+SETS+(...)|code-block|syntax|javascript|1377873|我想你已经在使用存储过程了。这样，至少可以在第二个过程中保持主逻辑完全静态，并且只需与一个过程与动态exec调用进行接口，以完成列的重命名。|offset|length|style|CODE|1377874|entityMap^0|0|0|1E|4|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If you've got a later version of SQL Server then you might look at this option:

<pre><code>EXEC &lt;stored_procedure&gt; WITH RESULT SETS (...)
</code></pre>

I imagine you're already using a stored procedure. This way you'd at least be able to keep the main logic completely static in a second procedure and just interface to a procedure with the dynamic <code>exec</code> call to accomplish the renaming of column(s).

Is there a way to name a column the value of what's in a parameter, without using dynamic SQL?

I need to somehow output the value of what is in the <code>@input</code> parameter as the name of the column in the SQL statement. I need to avoid using dynamic SQL.

<pre><code>DECLARE @input VARCHAR(10)=' Person '; 

SELECT count(*) AS @input + ' Open Data' 
FROM #Accounts a
JOIN dbo.FileFeed t On t.ID = a.AccountID 
GROUP BY a.accountid
</code></pre>

Make value of a parameter the column name without dynamic SQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

是否有一种方法可以在不使用动态SQL的情况下将参数中的值命名为列？我需要将@input参数中的值作为SQL语句中列的名称输出。我需要避免使用动态SQL。DECLARE @input VARCHAR(10)=' Person '; SELECT count(*) AS  @input + ' Open Data'    FROM #Accounts aJOIN dbo.FileFeed t On t

问将参数的值设置为不带动态SQL的列名。
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将参数的值设置为不带动态SQL的列名。EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将参数的值设置为不带动态SQL的列名。
EN