当前位置: 首页 > 面试题库 >

exec失败,因为名称不是有效的标识符?

况野
2023-03-14
问题内容

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

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

而且这不应该是因为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?



 类似资料:
  • 我正在尝试对我的进行测试,但是它总是说找不到用户名而失败。我已经将这个问题缩小到中检查是否找到User==null的行。 这里是我的 还有我的 而我的 失败的行是在中,它试图找到UserByEmail,因为userRepository被嘲笑了,它只是返回null并抛出username not search异常。如果我删除了Mock,那么我的测试失败了,因为它找不到中构造函数所需的UserRepos

  • 问题内容: 我的一个孩子正在读高中的Java,并在他的一项测试中接受了此测试: 以下哪项是Java中有效的标识符? 一个。 b。 C。 d。 e。 他回答 b 并弄错了。 我看着这个问题,并争辩说这 是 一个有效的标识符,应该是正确的。 我们看了一下Java 规范的标识符,它加强了这一点。我们还编写了一个示例程序,其中包含一个名为的变量以及一个方法。他创建了一个书面反驳,其中包括Java文档参考,

  • 我的一个孩子在高中学习Java,在他的一次考试中有这样的问题: 以下哪一个是Java中的有效标识符? a. b。 C。 D。 e. 他答b,答错了。 我研究了这个问题,认为是一个有效的标识符,它应该是正确的。 是有效的标识符吗?

  • 问题内容: 有什么技巧可以将Java保留字用作变量,方法,类,接口,包或枚举常量名称? 问题答案: 不,没有办法。这就是为什么它们被标记为“保留”的原因。

  • 我试图从包含日期列的.xlsx电子表格中导入数据。在这些列中,日期以DD-MON-YY格式显示(例如:20-aug-12)。 编辑:我的约会语言匹配器设置为“法语”。我可以改成“美国”吗?

  • Jenkins将良好的构建标记为失败,因为某些单元测试失败。如果我在没有单元测试的情况下运行构建,我将获得构建成功状态。如何配置Jenkins来执行以下操作: