Set vs Select to assign local variables?

[ulwitch]

When do you use Set to assign a value to a variable, and when do you use Select?

Set @var1 = 50
or
Select @var1 = 50

What if you need to result of a query, like this one:
Select @var1 = count(*) from table1

 

[BuGlen]

Found at:

http://www.tkdinesh.com/faq/ans/setorselect.html

-----
Q: SET or SELECT -- is there any performance difference?

A:There isnt any performance difference.But SET is the standard assignment syntax.
For example, consider this subquery:

SET @variable = (SELECT columnvalue FROM dbo.tablename)
Here, if the results of the subquery expression is empty then @variable has a value of NULL but
if results of the subquery are more than one row then you get a error.And thus it makes sense while

SELECT @variable = columnvalue FROM dbo.tablename
returns a value from a number of rows and we are not sure whats the criteria in selecting that row!

Also, for ease of programming SELECT is anyways used as SQL syntax to retrieve rows.Thus it makes it easier to have SET for assigning.One major difference in assigning using SET and SELECT is:

SET @variable1 = 'somevalue', @variable2 = 'someothervalue'
This is NOT possible but the same is possible with SELECT as in:

SELECT @variable1 = 'somevalue', @variable2 = 'someothervalue'
-----

Hope this helps.

Glen Appleton

VB.Net student.

 

[SQLBill]

According to the BOL, SET @local_variable should be used for variable assignment rather than SELECT @local_variable.

You can use:

SET @var1 = (SELECT COUNT(*) FROM table1)

Note that the SELECT statement is in parenthesis.

-SQLBill

BOL = Books OnLine = SQL Server Help
Installed as part of SQL Server's Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

 

[JohnDTampaBay]

SET will assign a value to single variable. Use SELECT to assign values to multiple variables in one statement or if you want to capture the result of a query.

No difference
Set @var1 = 50
Select @var1 = 50

Valid
Select @var1 = count(*) from table1
Select @var1 = 1, @var2 = 2

Invalid
Set @var1 = count(*) from table1
Set @var1 = 1, @var2 = 2


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.