exec失败，因为名称不是有效的标识符？

Question

我有一个查询，我需要将其作为动态查询来运行，以输出有意义的列名。例如，如果我直接运行查询，它将正确返回数据。但是，如果我使用下面的代码，它将显示：

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;

是因为加入了吗？

Answer 1

最后尝试以下方法：

exec (@query)

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

或者

EXECUTE sp_executesql @query

而且这不应该是因为FULL JOIN。
但我希望您已经创建了临时表：＃TrafficFinal，＃TrafficFinal2，＃TrafficFinal3。

请注意，在使用EXEC和sp_executesql之间有性能方面的考虑。因为sp_executesql像sp一样使用强制语句缓存。
更多细节在这里。

On another note, is there a reason why you are using dynamic sql for this
case, when you can use the query as is, considering you are not doing any
query manipulations and executing it the way it is?