Solved

Difference between SELECT and SET?

Posted on 2000-03-14
6
544 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Jeremy_D
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 25 total points
ID: 2616253
SELECT allows you to affect more variables using one statement,
SET allows to affect a cursor to a local variable,
for the rest they are the same
0
 
LVL 4

Expert Comment

by:mitek
ID: 2616718
i also found out that SELECT @var = 'value' is slightly faster than SET @var = 'value'
0
 
LVL 6

Expert Comment

by:crsankar
ID: 2618715
Select gives an option for assigning a variable with the value of a column in table whereas set do not allow this.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2618802
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
0
 
LVL 6

Expert Comment

by:crsankar
ID: 2618855
Ofcourse, when you are assigning a value to a variable you can assign only a single value.
0
 
LVL 4

Author Comment

by:Jeremy_D
ID: 2618887
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.



0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now