Set or Select

Question

Post reply

Set or Select

terry.holland

SSC Rookie

Points: 25
More actions
February 22, 2008 at 3:31 am

#69606

Could someone tell me the difference between the following two syntacts
--Using Set
Declare @MyVar int
Set @MyVar=1000
--Using Select
Declare @MyVar int
Select @MyVar=1000
Are there situations in which Set should be used and others wher Select should be?
Are there implications, performance or otherwise, of using one over the other?
tia
Terry

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 1

have a look at this

http://ryanfarley.com/blog/archive/2004/03/01/390.aspx

Hope it helps

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 2

Edward (2/22/2008)
have a look at this
http://ryanfarley.com/blog/archive/2004/03/01/390.aspx
Hope it helps

Pretty good explanation of SET vs SELECT... horrible method of creating a split function. Use a Tally table, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

terry.holland SSC Rookie Points: 25 More actions · Answer 3

i'll check when I get home - blogs are blocked at work

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 4

Hi,

Is it a homework???:-)

Set can be used only to intialize a single variable.

Select can be used to intialize a single variable or more than that..

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

And something for the future, if you will move to SS2008 🙂

I like using select statements when initializing a large number of freshly declared variables. This results in fewer lines of T-SQL. SS2008 will allow to skip even the select line, as it finally allows you to set variable values in the declaration statement. E.g.

declare @a int = 1, @b-2 int = 2

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

J-440512 SSCertifiable Points: 6285 More actions · Answer 6

Haaa... I like that new SQL 2008 declare and set function.

One more thing to answer your question. Let's say you need both the number of rows affected and the error status code right after an UPDATE. You have to use

DECLARE @li_RowCount int, @li_ErrCode int

SELECT @li_RowCount = @@ROWCOUNT, @li_ErrCode = @@ERROR

BECAUSE

SET @li_RowCount = @@ROWCOUNT

SET @li_ErrCode = @@ERROR

Fails.

After the first SET is executed, the @@ERROR value for the UPDATE statement is LOST. The second @@ERROR value is now what happened when the fist SET command was executed.

This is one specific case where SELECT is preferable over the SET statement even when setting a single local variable

Otherwise, when assigning a value to a local variable, I prefer to use the SET keyword.

Again this is a personal preference.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 7

And one more thing for the purists 🙂 Using SELECT to assign variables in not part of the SQL standard. For some it does matter 🙂 Though sometimes using SELECT is really useful (like J mentioned)

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software