Set vs. Select

[EdwinGene]

BOL recommends that you use 'Set @local_variable = scalar_value' instead of 'Select @local_variable = scalar_value', but it doesn't say why. Can someone tell me why 'Set' is recommended over 'Select'?

Thank you.

 

[swampBoogie]

Using set for assigning values complies with standard SQL. Select does not.

 

[vongrunt]

Yup, SET is by ANSI standard.

SET means "assign value", as in UPDATE statement. Mixing SELECT and SET for the same purpose makes code a little bit less readable.

In some cases SET is also useful to identify logical error ASAP. For example, this code:

set @blah = (select somefield from sometable where somekey=somevalue)

... will crash if subquery return more than 1 value. SELECT @blah = somefield... won't. Unfortunately, this coding style cannot be used to assign many variables at once with a single query.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]