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


TSQL variable


TSQL variable

Author
Message
Muhammad Tariq
Muhammad Tariq
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 245
Comments posted to this topic are about the item TSQL variable

Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
shnizzle
shnizzle
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 28
Since isnull(@var,1)=1 then the following code should be equivalent to the original:


Declare @var int
Select @var = 1+ Value1
From (Select 1 Value1 Union All Select 1 Union All Select 2) as a
Select @var


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?
shini2007
shini2007
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 38
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 5
but in @var=1+value1
the @var1 is always 1
so ans is 3
shnizzle
shnizzle
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 28
Right Smile
Thanks.
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13111 Visits: 12151
shnizzle (8/29/2008)
Since isnull(@var,1)=1 then the following code should be equivalent to the original:


Declare @var int
Select @var = 1+ Value1
From (Select 1 Value1 Union All Select 1 Union All Select 2) as a
Select @var


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?


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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
shnizzle
shnizzle
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 28
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:


SET @ComplaintList = ''

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

Return @ComplaintList


And it does iterate over all the relevant rows and gives the expected result.
Where can I find more about this?
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13111 Visits: 12151
shnizzle (8/29/2008)
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:


SET @ComplaintList = ''

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

Return @ComplaintList


And it does iterate over all the relevant rows and gives the expected result.
Where can I find more about this?


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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
shnizzle
shnizzle
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 28
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'.
nancy.lytle
nancy.lytle
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 699
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
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13111 Visits: 12151
shnizzle (8/29/2008)
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'.


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. Smile


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
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