Jeremy_D
asked on
Difference between SELECT and SET?
What is the difference (if any) between these two syntaxes:
SET @LocalVar = expression
SELECT @LocalVar = expression
when 'expression' is a non-SQL statement, for instance:
DECLARE @myvar varchar(255)
DECLARE @myint int
SET @myvar = 'First SET; '
SELECT @myvar = @myvar + 'First SELECT; '
SET @myvar = @myvar + 'Second SET; '
SELECT @myvar = @myvar + 'Second SELECT.'
SET @myint = 7
SELECT @myint = @myint * 18
SET @myint = @myint / 63
SELECT @myint = @myint - 2
SET @LocalVar = expression
SELECT @LocalVar = expression
when 'expression' is a non-SQL statement, for instance:
DECLARE @myvar varchar(255)
DECLARE @myint int
SET @myvar = 'First SET; '
SELECT @myvar = @myvar + 'First SELECT; '
SET @myvar = @myvar + 'Second SET; '
SELECT @myvar = @myvar + 'Second SELECT.'
SET @myint = 7
SELECT @myint = @myint * 18
SET @myint = @myint / 63
SELECT @myint = @myint - 2
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i also found out that SELECT @var = 'value' is slightly faster than SET @var = 'value'
Select gives an option for assigning a variable with the value of a column in table whereas set do not allow this.
crsankar: Try to do this:
SET @var = ( SELECT COLUMN FROM TABLE WHERE .. )
the select must return 1 row or an error occurs, but for the rest it works
SET @var = ( SELECT COLUMN FROM TABLE WHERE .. )
the select must return 1 row or an error occurs, but for the rest it works
Ofcourse, when you are assigning a value to a variable you can assign only a single value.
ASKER
angelIII: What I expected, thanks for the confirmation.
mitek: Thanks for the tip. Up to now I've used SET for assigning values to variables, and SELECT for SQL-statements, purely for esthetical purposes. I'll do a little test, and if you're right, it's back to using SELECT for all purposes again.
mitek: Thanks for the tip. Up to now I've used SET for assigning values to variables, and SELECT for SQL-statements, purely for esthetical purposes. I'll do a little test, and if you're right, it's back to using SELECT for all purposes again.