SELECT SQL Variable - should i avoid using this syntax and always use SET?
- by Sholom
Hi All,
This may look like a duplicate to here, but it's not. I am trying to get a best practice, not a technical answer (which i already (think) i know).
New to SQL Server and trying to form good habits.
I found a great explanation of the functional differences between SET @var = and SELECT @var =
here: http://vyaskn.tripod.com/differences_between_set_and_select.htm
To summarize what each has that the other hasn't (see source for examples):
SET:
ANSI and portable, recommended by Microsoft.
SET @var = (SELECT column_name FROM table_name) fails when the select returns more then one value, eliminating the possibility of unpredictable results.
SET @var = (SELECT column_name FROM table_name) will set @var to NULL if that's what SELECT column_name FROM table_name returned, thus never leaving @var at it's prior value.
SELECT:
Multiple variables can be set in one statement
Can return multiple system variables set by the prior DML statement
SELECT @var = column_name FROM table_name would set @var to (according to my testing) the last value returned by the select. This could be a feature or a bug. Behavior can be changed with SELECT @j = (SELECT column_name FROM table_name) syntax.
Speed. Setting multiple variables with a single SELECT statement as opposed to multiple SET/SELECT statements is much quicker. He has a sample test to prove his point. If you could design a test to prove the otherwise, bring it on!
So, what do i do?
(Almost) always use SET @var =, using SELECT @var = is messy coding and not standard.
OR
Use SELECT @var = freely, it could accomplish more for me, unless the code is likely to be ported to another environment.
Thanks
