April 23, 2014 at 5:22 am
Hello, Is there a difference in the following: DECLARE @T1 VARCHAR(50);
SET @T1 = (SELECT TOP(1) Col1 FROM MyTable); -- define variable first way
SELECT TOP(1) @t1 = Col1 FROM MyTable; -- define variable second way
I did a quick test and not finding a difference, however I was told that if nothing is returned by the query the result would be different and that the first way is better.
Thank you
April 23, 2014 at 5:31 am
Check this on technet
😎
April 23, 2014 at 5:45 am
Eirikur Eiriksson, I read the info and it does not give a reason.
technet
It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable. For more information, see SET @local_variable.
It most likely is because of the multiple rows, but it does not say that.
April 23, 2014 at 7:07 am
djj (4/23/2014)
Eirikur Eiriksson, I read the info and it does not give a reason.technet
It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable. For more information, see SET @local_variable.
It most likely is because of the multiple rows, but it does not say that.
You must have not have read further along. The basic gist of it is that if you are going to assign a second value to a your variable you need to be careful.
Let's take the example from technet and expand it a little bit to demonstrate clearly what is happening. I will assume you don't have an object with the name 'ALFKA' in your database. If you do, just change that to something that doesn't exist in your database.
DECLARE @var1 nvarchar(30)
SELECT @var1 = 'Generic Name'
SELECT @var1 = Name
FROM sys.objects
WHERE name = 'ALFKA'
SELECT @var1 AS 'Company Name'
Whoa!!! It seems logical that @var1 should now be null right??? However, there was nothing returned from the query so the value of the variable is unchanged.
Now if we use this same example but modify the sys.objects portion it will set the value to null.
DECLARE @var1 nvarchar(30)
SELECT @var1 = 'Generic Name'
set @var1 =
(
SELECT Name
FROM sys.objects
WHERE name = 'ALFKA'
)
SELECT @var1 AS 'Company Name'
This behavior is why it is often suggested to use this second method for setting variable values. Hope that make it a bit more clear.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2014 at 7:20 am
Thank you!
I find reading MS help a bit confussing as there are a lot of see something else. If you are lucky they even include a link.
Your explination and examples helped. My quick test did not properly take into count the redefine of the variable.
Thanks again.
April 23, 2014 at 7:26 am
djj (4/23/2014)
Thank you!I find reading MS help a bit confussing as there are a lot of see something else. If you are lucky they even include a link.
Your explination and examples helped. My quick test did not properly take into count the redefine of the variable.
Thanks again.
Glad that helped your understanding. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2014 at 7:41 am
I use select because I'm flat out lazy:
declare @i int,@j int
------1------------
set @i=1
set @j-2=2
------2------------
select @i=1,@j=2
-------------------
--and you can do:
declare @i int,@j int
select @i=1,@j=2,@i=@i+1,@j=@i+@j
select @i,@j
Which makes more sense if you're iterating over a data set.
April 23, 2014 at 7:47 am
djj (4/23/2014)
Hello, Is there a difference in the following:DECLARE @T1 VARCHAR(50);SET @T1 = (SELECT TOP(1) Col1 FROM MyTable); -- define variable first way
SELECT TOP(1) @t1 = Col1 FROM MyTable; -- define variable second way
I did a quick test and not finding a difference, however I was told that if nothing is returned by the query the result would be different and that the first way is better.
Thank you
In addition to the issue pointed out by Sean, that the variable will not get assigned if there is no result returned from MyTable, you also need to supply an ORDER BY clause when using the TOP clause, or else the specific value returned won't be deterministic.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 23, 2014 at 7:52 am
Eric M Russell (4/23/2014)
In addition to the issue pointed out by Sean, that the variable will not get assigned if there is no result returned from MyTable, you also need to supply an ORDER BY clause when using the TOP clause, or else the specific value returned won't be deterministic.
Thank you for the information.
This was for a quick test and I did not care what was selected, just that something was selected. My test was on a PK. For the test I even used WHERE 1 = 2.:-)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply