SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL variable


TSQL variable

Author
Message
shnizzle
shnizzle
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 28
Nancy, it was just a snippet. here's the whole function:

CREATE FUNCTION [dbo].[fnConcatUserComplaints] (@UserId int)

RETURNS varchar(8000)

AS BEGIN

DECLARE @strReturn VARCHAR(max)

SET @strReturn = ''

SELECT @strReturn = @strReturn+ ComplaintDetail+ char(13)
FROM ComplaintsTable
WHERE UserId=@UserId

RETURN @strReturn

END
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11132 Visits: 12004
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


Hi Nancy,

This code was not posted by me, but by Shnizzle.

Code such as this is not documented and not guaranteed to do what you might expect (and frankly, I don't even know exactly WHAT you expect, as there's more than one result that can be argued to be correct). However, many people seem to report consistent results and choose to rely on this undocumented construction.

Maybe you can post your actual code, along with CREATE TABLE statements for your tables, INSERT statemtents for your test data, and the required results. There might be a better way to achieve what you need.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1923 Visits: 1950
Interesting technique! I can think of places where I can use this. Thanks!
john.arnott
john.arnott
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2012 Visits: 3059
To add weight to Hugo's argument, here's a bit from BOL that seems relevant:
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


Now, of course the QOD result doesn't depend on the order in which the rows are evaluated, but it does depend on all the rows being evaluated. In the BOL example, the lowest EmployeeID is placed in the variable, apparently because it's the last one evaluated by a query that examines each row in table Employees in descending order by Employee.

But what if someone at Microsoft were to improve the performance of this query by tweaking the optimizer to take advantage of an index on EmployeeID and simply return the equivalent of min(EmployeeID)? The query behavior would still meet the description in BOL, only run a bit faster.

Then, even without an "order by" clause, it may be reasonable for the query to still use the value of the last row, even if that is by rule unpredictable. That brings us back to Hugo's point, which is that it can be dangerous to depend upon undocumented behavior of a db engine. By the rules, he's right that the answer could well be 2 or 3, depending on the implementation of the documented expected results for a query in this form.
nancy.lytle
nancy.lytle
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 698
Sorry about that, actually I was just thinking it would be another way to gets lists, but I can do that with regular queries. I just hadn't used that type of technique and thought it looked interesting. But since I can't get it to work in SS2005, and some of you are saying it can't be trusted, I will abandoned my 'playing' with it.

Thanks,
Nancy
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1923 Visits: 1950
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. Smile


Ok, Hugo, I get what you are saying about officially documented code. That is a good rule to follow.
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 1499
I'm not sure whether to be confused or intrigued. Good QOD.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

J-440512
J-440512
Right there with Babe
Right there with Babe (749 reputation)Right there with Babe (749 reputation)Right there with Babe (749 reputation)Right there with Babe (749 reputation)Right there with Babe (749 reputation)Right there with Babe (749 reputation)Right there with Babe (749 reputation)Right there with Babe (749 reputation)

Group: General Forum Members
Points: 749 Visits: 949
Well, I am confused.

Whatever happened to the concept that you cannot assign a result set to a scalar -- Should this not be barfed out at compile time in the first place ? I certainly would prefer
it be so, in case I make an error while coding something like this, rather than have this bizarre and not necessarily predictable behavior.

If I remember previous versions, it used to be that way.

But even when doing this for a string instead of an integer, it still results in a long string which concatenates all of the values.

Regards
david.wright-948385
david.wright-948385
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1252 Visits: 979
I reckon it's a good example of bad practice just because the outcome isn't defined :-)
Muhammad Tariq
Muhammad Tariq
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 245
(with apology)
and i reckon, good programming skills are needed to understand this code.

Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search