SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SET versus SELECT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shapper
Constraint Violating Yak Guru

446 Posts

Posted - 02/12/2007 :  11:01:17  Show Profile  Reply with Quote
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

Sweden
30113 Posts

Posted - 02/12/2007 :  11:06:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
None, except SET is slightly faster for single-variable assignment.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 02/12/2007 :  11:28:56  Show Profile  Visit spirit1's Homepage  Reply with Quote
why?



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/12/2007 :  11:30:31  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 02/12/2007 :  11:33:12  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Edited by - spirit1 on 02/12/2007 11:33:34
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/12/2007 :  11:36:46  Show Profile  Reply with Quote
... and

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

Edited by - Kristen on 02/12/2007 11:38:14
Go to Top of Page

shapper
Constraint Violating Yak Guru

446 Posts

Posted - 02/12/2007 :  11:41:50  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 02/12/2007 :  11:44:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You should

RETURN @@Error

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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

shapper
Constraint Violating Yak Guru

446 Posts

Posted - 02/12/2007 :  12:02:45  Show Profile  Reply with Quote
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


Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 02/12/2007 :  12:05:35  Show Profile  Reply with Quote
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

Edited by - Srinika on 02/12/2007 12:11:30
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 02/12/2007 :  13:05:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
http://www.sqlmag.com/Articles/ArticleID/94555/94555.html


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/12/2007 :  13:27:49  Show Profile  Reply with Quote
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




Edited by - X002548 on 02/12/2007 14:41:15
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/12/2007 :  14:39:18  Show Profile  Reply with Quote
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



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36613 Posts

Posted - 02/12/2007 :  14:59:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://vyaskn.tripod.com/differences_between_set_and_select.htm

Tara Kizer
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/12/2007 :  18:56:07  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36613 Posts

Posted - 02/12/2007 :  19:00:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
I agree. I use SELECT only when setting multiple variables, else SET.

Tara Kizer
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/13/2007 :  02:11:34  Show Profile  Reply with Quote
I find it convenient to stick to using SELECT throughout for "setting" one, or more, variables - rather than chop-and-change.

MOO

Kristen
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 02/13/2007 :  02:54:11  Show Profile  Reply with Quote
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.


KH

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/13/2007 :  10:26:27  Show Profile  Reply with Quote
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



Go to Top of Page

sanjev.sharma
Starting Member

10 Posts

Posted - 04/20/2008 :  03:06:07  Show Profile  Visit sanjev.sharma's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000