exec失败,因为名称不是有效的标识符?
问题描述:
我有一个查询我需要运行它作为动态查询输出一个有意义的列名称。作为一个例子,如果我直接运行查询,它会正确返回数据。但是,如果我用下面的代码,它显示:exec失败,因为名称不是有效的标识符?
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假定该变量的值是一个存储过程的名称。
OR
EXECUTE sp_executesql @query
,它不应该是因为FULL JOIN的。
但我希望你已经创建了临时表:#TrafficFinal,#TrafficFinal2,#TrafficFinal3在此之前。
请注意,使用EXEC和sp_executesql之间有性能方面的考虑因素。因为sp_executesql像sp一样使用强制语句缓存。
更多详细信息here。
在另一方面,是有一个原因,你正在使用动态SQL这种情况下,你可以直接使用的查询,考虑你是不是做任何的查询操作,并执行它,它是这样的?
答
正如在我的情况下,如果通过连接产生的SQL或用途转换,然后在执行需要SQL来与字母N前缀如下
例如
Exec N'Select bla..'
N定义字符串文字是unicode。
对不起。我花了一段时间才弄清楚可以点击检查符号。 – urlreader 2012-08-16 17:20:19