March 9, 2005 at 2:17 pm
I have seen syntax in client code and in BOL of both using the SET statement and the SELECT statement to assign values to variables, but unable to find anything that tells me when one syntax may be required versus the other? Can someone please help me find these rules if they are documented? Thanks!
Examples (I believe these both are valid if @variable has been declared):
SET @variable = (SELECT column from table)
versus
SELECT @variable = (SELECT column from table)
March 9, 2005 at 2:28 pm
The short version :
SET is the AISNI 92 standard and you should use it unless you are doing err handling. In which case you have no choice but to use this syntax :
Select @MyErr = @@error, @rwcnt = @@rowcount to catch both global variables in one step... because if you don't then the variables are reset right after you assign to a variable.
March 9, 2005 at 2:41 pm
That is helpful. I had a client claim that they needed to use a SELECT to set a value within a stored proc, or was it a user defined function? Anyway, I was curious if the rules did vary at all depending upon where the variable value setting was being done. Thanks!
March 9, 2005 at 7:13 pm
There was a really good discussion on this about 4months to a year ago. Sorry do not have the link right off.
March 10, 2005 at 5:14 am
To sum it up very briefly - both is allowed, SET is recommended and SELECT is more efficient and more flexible :-).
I mostly use SET only when setting fixed value of one variable (SET @param = 1) and SELECT when doing a select to find the value to be assigned, or when setting several variables at once. But that's just a matter of my personal taste, not recommendation for everybody :-)).
March 10, 2005 at 6:16 am
I wish I didn't lose the link to this great article about sets vs selects but :
"To sum it up very briefly - both is allowed, SET is recommended and SELECT is more efficient and more flexible :-)."
SELECT is NOT more efficient than set. The only way it'll be more efficient is when you do a pretty big loop in which you have to set multiple variables
while @i < 10000
begin
set @a = @i
set @b-2 = @i*2
set @C = @i*3
set @i = @i + 1
end
will run slower than this
while @i < 10000
begin
Select @a = @i, @b-2 = @i*2, @C = @i*3
set @i = @i + 1
end
because sql servers will have to do 3 small steps to assign the variables instead of 1 bigger step. but if you run this code only once in a sp, you won't see any difference (unless you run the sp a few 1000s or 1 000 000s times per day.
March 11, 2005 at 7:21 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply