Prev: String Manipulation: REPLACE multiple variables at once through a Join: How to
Next: Help with date
From: bill on 18 Dec 2009 14:32
Thanks everyone for the the replies. The SELECT statement that Hugo
showed looks terrific, and I would really like to use it, except for
the concerns raised in the KB article.

The article is not worded very clearly. However, when I read that
article, it appears that the "undefined" concern applies _only_ in the
case where functions are used in the WHERE clause.

Here is the sentence from the second paragraph that makes me think the
problem is related to WHERE clauses only:
"When an expression is applied to a member of an ORDER BY clause, that
resulting column is not exposed in the SELECT list, resulting in
undefined behavior."

IF this is the case (a big if), THEN I can use this SELECT statement
and not bother with the cursors.

Do people agree with my reading, or am I missing something?

Thanks,

Bill
From: Erland Sommarskog on 18 Dec 2009 17:16
Plamen Ratchev (Plamen(a)SQLStudio.com) writes:
> The article describes the behavior of using ORDER BY as undefined. Even
> recommends the approach that Hugo used as workaround solution (of
> course, the query doesn't use ORDER BY):

The article is certainly a bit confusing. But I prefer to hold on to
the first sentence:

The correct behavior for an aggregate concatenation query is undefined.

Nevermind that the article then stands on a head to state there is a
situation where it works nevertheless.

And usually it does work. But everyonce in a while, you see posts from
people who have been bitten.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: bill on 19 Dec 2009 00:13

Thanks everyone. Based on Erland's clarification, I went with the
cursor. It works fine, because the table is small. I just HATE
cursors, which is why I was trying to avoid one. Maybe in a future
release they will fully define the query behavior?

Thanks,

Bill
 | 
Pages: 1
Prev: String Manipulation: REPLACE multiple variables at once through a Join: How to
Next: Help with date