我有一个查询,我需要将其作为动态查询运行,以输出有意义的列名.例如,如果直接运行查询,它将正确返回数据.然而,如果我使用下面的代码,它会显示:

The name '
            SELECT (CASE WHEN A.Domain IS NOT NULL THEN A.Domain ELSE B.Domain END) AS [Domain], 
                    (CASE WHEN A.Email IS NOT NULL THEN A.Email ELSE B.Email END) AS [Email], 
                    A.[Sender Size] AS [Sender Size 1], A.[Sender Count] AS [Sender Count 1],
                        A.[Receiver Size] AS [Receiver Size 1], A.[Receiver Count] AS [Receiver Count 1],
                    A.[Sender Size 2] AS [Sender Size 2], A.[Sender Count 2] AS [Sender Count 2],
                        A.[Receiver Size 2] AS [Receiver Size 2], A.[Receiver Count 2] AS [Receiver Count 2],
                    B.SenderSize AS [Sender Size Average], B.SenderCount AS [Sender Count Average],
                        B.ReceiverSize AS [Receiv' is not a valid identifier.

下面是代码:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT *
            FROM
            (
                SELECT (CASE WHEN A.Domain IS NOT NULL THEN A.Domain ELSE B.Domain END) AS [Domain], 
                        (CASE WHEN A.Email IS NOT NULL THEN A.Email ELSE B.Email END) AS [Email], 
                        A.[Sender Size] AS [Sender Size 1], A.[Sender Count] AS [Sender Count 1],
                            A.[Receiver Size] AS [Receiver Size 1], A.[Receiver Count] AS [Receiver Count 1],
                        A.[Sender Size 2] AS [Sender Size 2], A.[Sender Count 2] AS [Sender Count 2],
                            A.[Receiver Size 2] AS [Receiver Size 2], A.[Receiver Count 2] AS [Receiver Count 2],
                        B.SenderSize AS [Sender Size Average], B.SenderCount AS [Sender Count Average],
                            B.ReceiverSize AS [Receiver Size Average], B.ReceiverCount AS [Receiver Count Average]
                FROM
                    (
                    SELECT (CASE WHEN tf.Domain IS NOT NULL THEN tf.Domain ELSE tf2.Domain END) AS Domain, 
                            (CASE WHEN tf.Email IS NOT NULL THEN tf.Email ELSE tf2.Email END) AS Email, 
                         ISNULL(tf.SenderSize,0) AS [Sender Size] , ISNULL(tf.SenderCount,0) AS [Sender Count], ISNULL(tf.ReceiverSize,0) AS [Receiver Size], ISNULL(tf.ReceiverCount,0) AS [Receiver Count], 
                         ISNULL(tf2.SenderSize,0) AS [Sender Size 2], ISNULL(tf2.SenderCount,0) AS [Sender Count 2], ISNULL(tf2.ReceiverSize,0) AS [Receiver Size 2], ISNULL(tf2.ReceiverCount,0) AS [Receiver Count 2] 
                    FROM #TrafficFinal tf FULL JOIN #TrafficFinal2 tf2 ON (tf.Email = tf2.Email AND tf.Domain = tf2.Domain)
                    ) A FULL JOIN #TrafficFinal3 B ON (A.Email = B.Email AND A.Domain = B.Domain)
            ) C
            ORDER BY Domain, Email';    

PRINT @query;

-- run it
exec @query;

是因为完全加入吗?

推荐答案

最后试试这个:

exec (@query)

如果没有括号,SQL Server将假定变量的值为存储过程名称.

EXECUTE sp_executesql @query

这不应该是因为完全连接


请注意,在使用EXEC和sp_executesql之间存在性能考虑.因为sp_executesql像sp一样使用强制语句缓存.


另一方面,考虑到没有进行任何查询操作,也没有按原样执行查询,那么在这种情况下使用动态sql有什么原因吗?

Sql相关问答推荐

使用子查询和连接更新PostgreSQL

PG SQL中按条件聚合值

我可以将INSERT语句与SELECT一起使用来创建条件吗?

如何在不更改S代码的情况下,判断存储过程调用了多少次clr函数?

过go 四周未填充的数据,即W50,51,52-SQL

获得第三名或最老的记录

根据最大值为字母数字大小写分配数值

如何设计一个调用嵌套函数并仅在所有被调用的嵌套函数都提交时才提交的事务,例如,如果一个子函数失败则中止?

ColdFusion+Docker:未安装SQLSERVER包

在Power Bi中将SQL代码转换为DAX

从重复值中获取最新值

Postgresql 具有相似行为和模式行为的问题

SQL Server中使用min()和max()从选定的特定值id表中删除不必要的时间

如何根据共同列值从两个表中包含列,但只包含左表中的行

如何使用Informix创建一个临时表,将数据从根表导入并使用筛选条件

如何通过CROSS APPLY获取多级嵌套JSON属性的值?

使用对 nvarchar 列的多个 LIKE 操作优化 SQL 查询

连续期间的缺口

以 15 分钟为间隔的使用情况SQL 查询

按 15 分钟递增计数分组,包括 0 计数