| Author |
 Topic  |
|
|
shapper
Constraint Violating Yak Guru
443 Posts |
 Posted - 02/12/2007 : 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
Sweden
29138 Posts |
Posted - 02/12/2007 : 11:06:28
|
None, except SET is slightly faster for single-variable assignment.
Peter Larsson
Helsingborg, Sweden |
 |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 02/12/2007 : 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
Slovenia
11741 Posts |
Posted - 02/12/2007 : 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 |
Edited by - spirit1 on 02/12/2007 11:33:34 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 02/12/2007 : 11:36:46
|
... and
UPDATE MyTable
SET @foo1 = MyColumn = @foo1 + 1, ... |
Edited by - Kristen on 02/12/2007 11:38:14 |
|
|
|
shapper
Constraint Violating Yak Guru
443 Posts |
Posted - 02/12/2007 : 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
Sweden
29138 Posts |
Posted - 02/12/2007 : 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
443 Posts |
Posted - 02/12/2007 : 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
Flowing Fount of Yak Knowledge
Sri Lanka
1378 Posts |
Posted - 02/12/2007 : 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
|
Edited by - Srinika on 02/12/2007 12:11:30 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 02/12/2007 : 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
|
Edited by - X002548 on 02/12/2007 14:41:15 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 02/12/2007 : 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
USA
35007 Posts |
Posted - 02/12/2007 : 19:00:17
|
I agree. I use SELECT only when setting multiple variables, else SET.
Tara Kizer |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 02/13/2007 : 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)
Singapore
16746 Posts |
Posted - 02/13/2007 : 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.
KH
|
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 02/13/2007 : 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 - 04/20/2008 : 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 |
|
|
| |
Topic |
|
SET versus SELECT
