SET versus SELECT - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SET versus SELECT

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-12 : 11:01:17

Hello,

I have an Output parameter as follows:
@Feedback INT OUTPUT

I want to give it a value and return it.
What is the difference between using:

SELECT @Feedback = -1
RETURN @Feedback

And

SET @Feedback = -2
RETURN @Feedback

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 11:06:28

None, except SET is slightly faster for single-variable assignment.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-12 : 11:28:56

why?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-02-12 : 11:30:31

SET @foo = (SELECT MyCol FROM MyTable WHERE ...)

will give error if SELECT returns more than one row.

whereas:

SELECT @foo = (SELECT MyCol FROM MyTable WHERE ...)
or
SELECT @foo = MyCol FROM MyTable WHERE ...

won't care.

"RETURN @Feedback"

Returning the value is best avoided, stick to using RETURN for Error Codes and return anything needed by the Application as an OUTPUT parameter (or a Result Set)

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-12 : 11:33:12

not to mention using
select @error = @@error, @rowcount = @@rowcount

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-02-12 : 11:36:46

... and

UPDATE MyTable
SET @foo1 = MyColumn = @foo1 + 1, ...

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-12 : 11:41:50

I am lost now. :-)

Basically all I have is an output parameter of type int which I use to return something as simple as:
SET @Feedback = -1
RETURN @Feedback

or

SET @Feedback = @@ERROR
RETURN @Feedback

So I suppose if I can use SET in both cases and if SET is faster than SELECT in this examples then I should use SET, right?

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 11:44:15

You should

RETURN @@Error

and give back the value of @Feedback to the OUT parameter.

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-12 : 12:02:45

Peso,

I don't understand your answer.

Basically I am checking various conditions.
When a condition is not verified I return the values -1, -2, ... in the @Feedback using:
SET @Feedback = -1
RETURN @Feedback

If all conditions are verified then I will insert a new record and return the error if any using the Feedback parameter:
SET @Feedback = @@Error
RETURN @Feedback

This is the method I saw in an article for the .NET Enterprise Library Data Access Application Block.

Then in my .NET code I am checking the value of Feedback to check which error I got.

Is this the wrong way to do this?

Thanks,
Miguel

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-02-12 : 12:05:35

Shapper,

1. Use Set as suggested.Its faster than Select - as per Peso, and OK with returning an error - as per spirit1
2. If u use just to return a value, try not to use ~~"Set"~~ "Return" as per Kristen

In ur situation, it doesn't seems to have the issue of Returning Multiple rows ....

But again make sure u use the "Output" in the Parameter list instead of "Return" in the Stored procedure
Srinika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 13:05:40

http://www.sqlmag.com/Articles/ArticleID/94555/94555.html

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-12 : 13:27:49

Well I can't find any per issues..I originally had the loop just reset the values, but I thought it have beensql server being clever, by "knowing" (how? I have know idea) the original value and potentially not having to do the set or select. in either case I added a RESET Select or Set so to force it...~~times are identical~~

I've run over 1 million iterations, and these are the times I get

SELECT_MS SET_MS
----------- -----------
43433 43433

I am going to blog the test

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-12 : 14:39:18

quote:
Originally posted by Srinika

Shapper,

1. Use Set as suggested.Its faster than Select

Actually I messed up the test. SELECT is actually faster in all cases

Have a look

http://weblogs.sqlteam.com/brettk/archive/2007/02/12/60090.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-12 : 14:59:10

http://vyaskn.tripod.com/differences_between_set_and_select.htm

Tara Kizer

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-02-12 : 18:56:07

This is a logical Q imo.

For single variable assignements always use SET.

If you are setting several variables in one step, SELECT will be faster,
however if you wantt to take advantage of the speed, ensure that the SELECT can only return ONE row!

___________________________________________
speed is good, correctness is better
if you sacrifice, know what you sacrifice

rockmoose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-12 : 19:00:17

I agree. I use SELECT only when setting multiple variables, else SET.

Tara Kizer

Kristen
Test

22859 Posts

Posted - 2007-02-13 : 02:11:34

I find it convenient to stick to using SELECT throughout for "setting" one, or more, variables - rather than chop-and-change.

MOO

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 02:54:11

quote:
Originally posted by Kristen

I find it convenient to stick to using SELECT throughout for "setting" one, or more, variables - rather than chop-and-change.

MOO

Kristen

MetOO. After reading one of your post.

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-13 : 10:26:27

Well since SELECT is faster in any case, I'll use SELECT

Just be awatre of any pitfalls, which both SET and SELECT both have

The only thing is that SET is ANSI, and this is the ONLY time I go against ANSI...hell even in COBOL you don't have to use MOVE more than once for multiple operations

SET Should work like DECLARE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sanjev.sharma
Starting Member

10 Posts

Posted - 2008-04-20 : 03:06:07

SET vs SELECT - Sql server

We always get confused between SELECT and SET when assigning values to variables, and make mistakes. Here in this article, I will try to highlight all the major differences between SET and SELECT, and things you should be aware of, when using either SET or SELECT.
http://sanjevsharma.blogspot.com/2008/04/set-vs-select-sql-server.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources