When to use SET, when to use SELECT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

When to use SET, when to use SELECT

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-20 : 12:56:45

...can someone post concise advice about when to use each of these? I'm lazy, so I generally use SELECT for everything, even just setting a variable to some constant value ("SELECT @i=3"). Is there any reason not to do that?

Thanks
-b

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-20 : 13:56:47

Me also.

set is the recommened method for setting a variable - probably due to ansi standards.
Maybe you should also

set @i = (select i rfom tbl)

But it only allows single variable assignments.
I use select because you can assign multiple variables and it it is less words to remember.
It's not something to worry about.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-04-20 : 20:12:02

If you're lazy, SET is 3 less characters to type

I tend to use SET unless

I'm assigning multiple variables and require it to be in one statement such as @@ERROR and @@ROWCOUNT after a DML statement where the variables would be reset if accessed in separate statements e.g.
```
select @err=@@ERROR, @cnt=@@ROWCOUNT
```
I'm populating a local variable from a select/DML statement e.g.
```
select @compname = CompanyName from dbo.Customers where CustomerId ='ALFKI'
```
(which according to the execution plan is 0.04% cheaper than using SET but I'd take that with a pinch of salt)

Basically it generally doesn't make the slightest bit of difference and whatever you are comfortable with is fine as long as the code is readable and easily understood.

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-04-21 : 08:49:05

As a newbie I prefer SET
There are usually enough SELECTS floating around to
confuse the heck out of a lot of folks in big procs.
I find SET explains the intent of the creator a little
less ambiguously, and makes for an easier read.

Voted best SQL forum nickname...."Tutorial-D"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-21 : 21:15:29

I also use SELECT for variables, for the reasons given (one command, multiple variables) but also because I'm almost always assigning values to a variable from a table, so I need SELECT anyway.

I also like to keep SET limited to actual settings in the system (SET ANSI_NULLS ON, SET ROWCOUNT 10, SET NOCOUNT ON, etc.) instead of assigning values. Just a personal preference, but it has helped to clarify code that had a mix of system (re)settings and variable settings.

Edited by - robvolk on 04/21/2003 21:16:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources