nancy.lytle (8/29/2008)

---

Hugo, about your code:

SET @ComplaintList = ''

SELECT @ComplaintList = @ComplaintList+ UserComplaint+ char(13)
FROM ComplaintsTable
WHERE UserID=@UserID
..

Return @ComplaintList

I thought that might be something I could use for various things, but I cannot get the code to work, even with the .. commented out. Is there something missing from the code (I of course substituted my name for yours)

Nancy

Caution:
If there are multiple assignment clauses in a single SELECT statement, SQL Server does not guarantee the order of evaluation of the expressions. Note that effects are only visible if there are references among the assignments.

If a SELECT statement returns more than one row and the variable references a nonscalar expression, the variable is set to the value returned for the expression in the last row of the result set. For example, in this batch @EmpIDVariable is set to the EmployeeID value of the last row returned, which is 1:

USE Northwind
GO
DECLARE @EmpIDVariable int

SELECT @EmpIDVariable = EmployeeID
FROM Employees
ORDER BY EmployeeID DESC

SELECT @EmpIDVariable
GO

Hugo Kornelis (8/29/2008)
If you still want to implement this and put it in production, feel free to do so ... but never forget that it IS undocumented, so you'll have to retest after every service pack, every patch, and every hardware update and still be prepared to be surprised. The fact that many users do use this and do rely on this does not guarantee that Microsoft won't change it - just remember what happened to GROUP BY without ORDER BY when upgrading from SQL 6.5 to SQL 7.0, or to views with TOP 100 PERCENT and ORDER BY when upgrading to SQL 2000 to SQL 2005. For my production code, I'll stick to officially documented code. :)