SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tariq / TSQL variable / Latest Posts

RE: TSQL variable

david.wright-948385 — Wed, 26 Nov 2008 10:09:38 GMT

I take your point about it being good exercise for the mind - quite so. But in the real world, if you rely on the database behaving in a particular way when that behaviour is not defined, aren't you asking for trouble? I wouldn't want code like that in a database I'm responsible for! After all, for each new release MS will test that the database conforms to their definition of correct (hopefully from the documentation, or at least the same source as the documentation)!), not the way it used to work.

RE: TSQL variable

Muhammad Tariq — Wed, 26 Nov 2008 09:17:58 GMT

(with apology)and i reckon, good programming skills are needed to understand this code.

RE: TSQL variable

david.wright-948385 — Wed, 26 Nov 2008 08:44:36 GMT

I reckon it's a good example of bad practice just because the outcome isn't defined :-)

RE: TSQL variable

J-440512 — Tue, 02 Sep 2008 11:56:55 GMT

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 preferit 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

RE: TSQL variable

Tom Garth — Sat, 30 Aug 2008 07:09:54 GMT

I'm not sure whether to be confused or intrigued. Good QOD.

RE: TSQL variable

Carla Wilson-484785 — Fri, 29 Aug 2008 13:47:09 GMT

[quote][b]Hugo Kornelis (8/29/2008)[/b]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. :)[/quote]Ok, Hugo, I get what you are saying about officially documented code. That is a good rule to follow.

RE: TSQL variable

nancy.lytle — Fri, 29 Aug 2008 12:35:51 GMT

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

RE: TSQL variable

john.arnott — Fri, 29 Aug 2008 12:28:33 GMT

To add weight to Hugo's argument, here's a bit from BOL that seems relevant:[quote]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 NorthwindGODECLARE @EmpIDVariable intSELECT @EmpIDVariable = EmployeeIDFROM EmployeesORDER BY EmployeeID DESCSELECT @EmpIDVariableGO [/quote]Now, of course the QOD result doesn't depend on the order in which the rows are evaluated, but it [b]does[/b] depend on [b]all[/b] 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.

RE: TSQL variable

Carla Wilson-484785 — Fri, 29 Aug 2008 09:26:06 GMT

Interesting technique! I can think of places where I can use this. Thanks!

RE: TSQL variable

Hugo Kornelis — Fri, 29 Aug 2008 08:50:37 GMT

[quote][b]nancy.lytle (8/29/2008)[/b][hr]Hugo, about your code:SET @ComplaintList = ''SELECT @ComplaintList = @ComplaintList+ UserComplaint+ char(13) FROM ComplaintsTableWHERE UserID=@UserID.. Return @ComplaintListI 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[/quote]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.

RE: TSQL variable

shnizzle — Fri, 29 Aug 2008 08:48:09 GMT

Nancy, it was just a snippet. here's the whole function:CREATE FUNCTION [dbo].[fnConcatUserComplaints] (@UserId int)RETURNS varchar(8000)AS BEGINDECLARE @strReturn VARCHAR(max)SET @strReturn = ''SELECT @strReturn = @strReturn+ ComplaintDetail+ char(13) FROM ComplaintsTableWHERE UserId=@UserIdRETURN @strReturn END

RE: TSQL variable

Hugo Kornelis — Fri, 29 Aug 2008 08:47:53 GMT

[quote][b]shnizzle (8/29/2008)[/b][hr]Hugo, I read the KB article and even when taking it into account, I think that my code is correct - it looks exactly as the statement described in the 'Workaround' section, sans the 'ORDER BY'. [/quote]Hi Shnizzle,Personally, I don't consider this article as implying that it SHOULD work, and that it ALWAYS WILL work. And I value very much the fact that the syntax "SELECT @var = @var + SomeColumn FROM ..." is not mentioned in Books Online at all very telling.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 ORDERY 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. :)

RE: TSQL variable

nancy.lytle — Fri, 29 Aug 2008 08:40:27 GMT

Hugo, about your code:SET @ComplaintList = ''SELECT @ComplaintList = @ComplaintList+ UserComplaint+ char(13) FROM ComplaintsTableWHERE UserID=@UserID.. Return @ComplaintListI 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

RE: TSQL variable

shnizzle — Fri, 29 Aug 2008 08:16:11 GMT

Hugo, I read the KB article and even when taking it into account, I think that my code is correct - it looks exactly as the statement described in the 'Workaround' section, sans the 'ORDER BY'.

RE: TSQL variable

Hugo Kornelis — Fri, 29 Aug 2008 07:36:50 GMT

[quote][b]shnizzle (8/29/2008)[/b][hr]Hugo, is it not documented or documented as not defined? This code reminded me of a function I use that operates on the same principle in order to concatenate column data over multiple rows for a certain UserID:[quote]SET @ComplaintList = ''SELECT @ComplaintList = @ComplaintList+ UserComplaint+ char(13) FROM ComplaintsTableWHERE UserID=@UserID.. Return @ComplaintList[/quote]And it does iterate over all the relevant rows and gives the expected result. Where can I find more about this?[/quote]Hi Shnizzle,As far as I know, the syntax of queries such as yours is not covered anywhere in Books Online. This by itself is sufficient for me to not use this in production code.In the knowledge base, there is an explicit article that warns about unexpected results if such queries depending on the exact location of an ORDER BY clause: http://support.microsoft.com/default.aspx/kb/287515. If you run the repro code, you'll see that in some cases the results are incomplete. Though this article is specifically about ORDER BY, I would never take this to imply that these queries are reliable if no ORDER BY is used.

RE: TSQL variable

shnizzle — Fri, 29 Aug 2008 05:41:52 GMT

Hugo, is it not documented or documented as not defined? This code reminded me of a function I use that operates on the same principle in order to concatenate column data over multiple rows for a certain UserID:[quote]SET @ComplaintList = ''SELECT @ComplaintList = @ComplaintList+ UserComplaint+ char(13) FROM ComplaintsTableWHERE UserID=@UserID.. Return @ComplaintList[/quote]And it does iterate over all the relevant rows and gives the expected result. Where can I find more about this?

RE: TSQL variable

Hugo Kornelis — Fri, 29 Aug 2008 05:25:13 GMT

[quote][b]shnizzle (8/29/2008)[/b][hr]Since isnull(@var,1)=1 then the following code should be equivalent to the original:[quote]Declare @var intSelect @var = 1+ Value1From (Select 1 Value1 Union All Select 1 Union All Select 2) as aSelect @var[/quote]But it isn't - the answer this time is 3. Why does the isnull function have the effect of causing the Value1 column to be summed into @var?[/quote]Hi Shnizzle,Very good point. The output from your version can be either 2 or 3, as there is no documentation on which row should be processed "last" (and hence, which of the possible results should "stick").And for the original query, those two answers (2 and 3) are actuallly just as correct as the more common answer (5). Because the behaviour of SELECT @var = @var + something is not documented, you can just as well defend that this should be evaluated for each row with the original value of @var, instead of the current observed behaviour that takes the new value of @var in account for each following row. And since it's undocumented, the alternative behaviour might just become the current behaviour on the next version, service pack, or maybe even after a bugfix or when the query processor decides on a different execution plan.

RE: TSQL variable

shnizzle — Fri, 29 Aug 2008 01:27:04 GMT

Right :)Thanks.

RE: TSQL variable

shini2007 — Fri, 29 Aug 2008 01:14:20 GMT

isnull function select the 1 whn @var is null or first time , second time @var=2 , n third time it will 3 n next so ans is 5but in @var=1+value1the @var1 is always 1so ans is 3

RE: TSQL variable

shnizzle — Fri, 29 Aug 2008 00:09:34 GMT

Since isnull(@var,1)=1 then the following code should be equivalent to the original:[quote]Declare @var intSelect @var = 1+ Value1From (Select 1 Value1 Union All Select 1 Union All Select 2) as aSelect @var[/quote]But it isn't - the answer this time is 3. Why does the isnull function have the effect of causing the Value1 column to be summed into @var?

TSQL variable

Muhammad Tariq — Thu, 28 Aug 2008 20:28:54 GMT

Comments posted to this topic are about the item [B]TSQL variable[/B]