December 3, 2009 at 8:55 am
Really stupid question: when it comes to assigning variables, what's the difference between SET and SELECT? Is one recommended over the other?
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
December 3, 2009 at 9:15 am
Okay, for anyone who cares, I just discovered one difference . . .
SELECT allows me to do this:
select @ErrNo = 0, @ErrMsg = 'error message'
However, when I try this (with SET), I get an error:
set @ErrNo = 0, @ErrMsg = 'error message'
Figured I'd post this, in the off-chance that someone might be looking for an answer to this.
Anyone know of any other differences (performance, etc.)?
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
December 3, 2009 at 9:34 am
Select allows for assigning multiple variables at the same time. It also allows for a direct Select From Where structure, while Set requires scalar sub-queries.
There's no significant performance difference. I've tested, and Set was a few milliseconds faster on millions of iterations. 5 or 10 milliseconds over millions of uses. But that was only using Select for static values, since that's all Set allows.
I consistently use Select. There are situations where you have to use Select and can't use Set, but there aren't any where you can't use Select and have to use Set. So, for consistency, I just use Select.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 3, 2009 at 9:49 am
The only "gotcha" is that doing:
DECLARE @var VARCHAR(10)
SELECT
@var = NAME
FROM
sys.databases AS D;
SELECT @var;
works even thought there are multiple rows returned, you just get a potentially random value in the variable. While doing:
The only "gotcha" is that doing:
DECLARE @var VARCHAR(10)
SET @var = (SELECT NAME FROM sys.databases);
SELECT @var;
fails with:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 3, 2009 at 9:58 am
Yes, you can define a query poorly and end up with unpredictable results for a variable. Easy enough to control for if you know what you're doing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 3, 2009 at 10:26 am
Okay, good to know. I'll definitely keep that in mind. Thanks!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply