Solved

Difference between SELECT and SET?

Posted on 2000-03-14
6
531 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

759 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

16 Experts available now in Live!

Get 1:1 Help Now