[RESOLVED] Set or Select?

[InvisibleDuncan]

On the MSDN site, I came across this statement:

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.

It doesn't give any context or reason for this. Does anyone know why that's the recommended syntax? It just seems a little clumsier to write:

Set @variable = (SELECT Value FROM Table)

... rather than:

Select @variable = Value FROM Table

Any opinions gratefully received...

[FunkyDexter]

It's because the set will throw an error if there is more than eligible value returned by the query. Select will simply assign the first eligible value (which will be random unless you're specifying an order). I agree with you that select looks more elegant but set is more reliable.

[InvisibleDuncan]

Thanks, my funky friend. That makes sense.

[techgnome]

I typically use the select because 1) I generally have a where clause because I'm after a specific row, and 2) Rarely am I getting just one field, more often than not, I need a few fields.

-tg

[FunkyDexter]

True, if you're doing multiple values then the select is certainly more efficient but you do need to be careful that the table design doesn't change and make the where clause non-unique - I've been caught that way before. It's yet another of those circumstances where there isn't an answer that's always correct. I personally favour the Set unless there's a pressing reason (ie multiple values in a sproc that needs to be very efficient) not to but it's horses for courses.

BTW, there is a third option which is to use select with a bracketed sub query. Something like:-

Select @MyVal = (Select myVal From myTable),
  @MyOtherVal = (Select anotherVal From aDifferentTable)

This will behave in the same way as set in that it will error if more than one value is returned but does have the advantage that you can set several values as part of a single query. Because they sub selects will be evaluated individually this is unlikely to give you any advantage over a series individual Set statements if you're putting it in a sproc but if you're issuing the statements from a client app it could save you a few server trips.