ORDER BY突然与TSQL中的VARCHAR串联冲突 [英] ORDER BY suddenly conflicting with VARCHAR concatenation in TSQL

问题描述

我在Sql Server 2008中有代码，它使用经过实践检验的 SELECT @VAR = @VAR + FROM TABLEOR BY OTHERFIELD 语法。

I have code in Sql Server 2008 which concatenates some strings from a query into a variable using the tried-and-true SELECT @VAR = @VAR + FIELD FROM TABLE ORDER BY OTHERFIELD syntax.

这是我的确切SQL：

SELECT @SQL = @SQL + 
    ISNULL(FORMULA, 
    CASE WHEN USEMAP = 1 THEN 
        'dbo.getFieldTranslation('+CONVERT(VARCHAR,ROWID)+', [' + ISNULL(ENCOMPASSFIELD,'') + '])' 
    ELSE 
        '[' + ISNULL(ENCOMPASSFIELD,'') + ']' END
    ) + 
    ' AS "' + FILECOLNAME + '",' + @CRLF  
FROM dbo.EXPORTMAP_EX 
WHERE WAREHOUSEID = @WHSID 
ORDER BY ORDERIDX

这是完美而完美的工作。然后今天突然停止了工作。它只是串联最后一行的值。在调试中，我发现如果删除 ORDER BY 子句，则所有字段都返回，但严格按字母顺序按字段的字符串值排序。

This was working beautifully and perfectly. Then suddenly today, it stopped working. It was only concatenating on the last row's values. In debugging, I found out that if I take out the ORDER BY clause, then all the fields come back, but sorted strictly alphabetically by the string value of the fields.

但是此SQL被用于生成导出视图。导出文件的字段顺序必须正确，因此必须使用ORDER BY子句。为什么ORDER BY突然用于 过滤 我的结果？

This SQL however is being used to generate a view for an export. The export file must have its fields in the proper order, hence the ORDER BY clause. Why is the ORDER BY suddenly serving to filter my results?

我不能使用XML / STUFF路由，因为某些数据确实具有<和>登录。

我宁愿不必回到使用旧的WHILE循环，但我可能不得不这样做。

I cannot use the XML/STUFF route because some of the data does have < and > signs in it.
I'd rather not have to go back to using an old WHILE loop, but I may have to.

推荐答案

如注释中所示，您所依赖的是未记录的行为。

As indicated in the comments you are relying on undocumented behaviour.

Microsoft说 正确聚合级联查询的行为未定义。。如果计算标量移到计划中的错误位置，则它将停止工作。

Microsoft say "The correct behavior for an aggregate concatenation query is undefined.". If the compute scalar moves to the wrong place in the plan then it just stops working.

您正在执行的工作是在子查询中定义排序表达式，然后将此查询的结果串联起来 I 相信是安全的。来自 SQL Server中的订购保证...

The work around you are using of defining the ordering expression in a sub-query and concatenating the results of this query I believe is safe . From Ordering guarantees in SQL Server...

出于向后兼容的原因，SQL Server为类型为SELECT @p = @p + 1的
分配提供支持。

For backwards compatibility reasons, SQL Server provides support for assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most scope.

该帖子写于2005年，所以我不确定如果保证仍然有效（编辑：在在此问题上查看Connect Items后我确定不会）

That posting was written in 2005 though so I'm not sure if the guarantee still holds ( After reviewing Connect Items on this issue I'm sure that it doesn't)

BTW：您说

我不能使用XML / STUFF路由，因为有些的数据确实具有<
和>签到。我宁愿不必再使用旧的
WHILE循环，但是我可能不得不。

I cannot use the XML/STUFF route because some of the data does have < and > signs in it. I'd rather not have to go back to using an old WHILE loop, but I may have to.

这并非如此，但是与您可能使用的 XML PATH 查询。 >在此处查看答案以获取示例。

This is not the case but it needs a slightly more complicated XML PATH query than you were probably using see the answer here for an example.

这篇关于ORDER BY突然与TSQL中的VARCHAR串联冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！