| Author |
 Topic  |
|
|
vivek.kumargupta
Starting Member
India
45 Posts |
 Posted - 09/25/2005 : 00:47:04
|
What is the difference between the SELECT and SET stmts in SQL Server 2000.What exactly is the purpose of SET stmt when almost everything can be done via SELECT stmt?Is there any instance when SELECT fails and SET works.[font=Arial][blue]
Thanks in Advance.
Vivek |
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/25/2005 : 02:04:03
|
select will actually return a value. Set just sets a value.
I only use select when I actually need to return data, I use SET everywhere else.
-ec |
 |
|
|
vivek.kumargupta
Starting Member
India
45 Posts |
Posted - 09/25/2005 : 02:24:42
|
As in
declare @var int
set @var =1
go
declare @var int
select @var =1
--Nothing is returned actually ... thus set and select works same as far as above assignment is considered.
My point is , there should be some redundancy in using SET than SELECT which i don't know...
Hope i am being clear.
Thanks,
Vivek |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 09/25/2005 : 02:37:30
|
If you need to do
SET @Var1 ='foo'
SET @Var2 ='bar'
...
you would be better off with
SELECT @Var1 ='foo',
@Var2 ='bar',
...
because it is faster.
I would prefer to use SET for setting @Variables because it would immediately imply to me the intent (i.e. I'm not selecting a recordset).
However, because multi-variable SET'ting is slower than using SELECTs I need to use SELECT some of the time, and a mixture is inconsistent, so I always use SELECT
Pity really.
Kristen |
|
|
|
sachinsamuel
Constraint Violating Yak Guru
India
383 Posts |
Posted - 09/25/2005 : 03:49:32
|
Vivek,
If you want to assign multiple variable in just one statement then use select else use Set.
For e.g
DECLARE @ERR INT
DECLARE @ROWS_AFFECTED INT
UPDATE TEST SET NAME = 'SACHIN SAMUEL' WHERE ID=8
SELECT @ERR=@@ERROR, @ROWS_AFFECTED=@@ROWCOUNT
-- In the above line I am assigning 2 variables in just one line.
Hope I am making sence!
Regards
Sachin Samuel |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/25/2005 : 04:02:14
|
quote:
Originally posted by vivek.kumargupta
As in
declare @var int
set @var =1
go
declare @var int
select @var =1
--Nothing is returned actually ... thus set and select works same as far as above assignment is considered.
My point is , there should be some redundancy in using SET than SELECT which i don't know...
Hope i am being clear.
Thanks,
Vivek
I must have been on crack when i said that. I could have sworn there was some funky difference like this between SET and SELECT, but I appear to be wrong.
Anyway, I found the definitive answer to this question, thanks to Vyas. http://vyaskn.tripod.com/differences_between_set_and_select.htm
-ec |
|
|
|
vivek.kumargupta
Starting Member
India
45 Posts |
Posted - 09/25/2005 : 08:33:53
|
Thanks Sachin for an instance where SELECT is handy and also to ec for an informative link.The link gives an exaustive comparisons between the two.
-Vivek
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 09/26/2005 : 01:16:24
|
I once had this doubt and cleared it by reading Vyas article
If you want to assign a value taking from a table, you can use
Set @var=(Select value from yourTable)
That will work as long as the query returns single value. Otherwise you will get error
But using Select is somewhat safer as you wont get error but the last value
Select @var=value from yourTable
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 09/26/2005 : 01:20:05
|
"I found the definitive answer to this question, thanks to Vyas"
Thanks for the 5-star link ec
Its a while since I read that article, and its reminded me of a couple of things I thought useful the first time I read it but have failed to adopt. I'll try again!
Also the fact that its very hard to do:
SELECT @MyErrNo = @@ERROR, @MyRowCount = @@ROWCOUNT
using SET !
Shame really ...
Kristen |
|
|
|
vivek.kumargupta
Starting Member
India
45 Posts |
Posted - 09/26/2005 : 21:50:04
|
So finally the conclusion is that SELECT is the right choice all the times be it performance or programming wise ...
But does that mean that SET was only created by MSFT to assign values to a single variable??I think MSFT must have done away with this SET at all!!!
:-)
-Vivek |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 09/27/2005 : 03:17:10
|
"I think MSFT must have done away with this SET at all"
SET is the ANSI standards compliant way to assign a value to a variable ... but SELECT is "better" in SQL Server, as there are a few things you just cannot do with SET - like capturing @@ROWCOUNT and @@ERROR after a statement - and for multi-variable assignments SELECT is faster.
However, as Vyas's article points out there are some things SET will do better - like raising an error if you attempt to assign multiple values to variable - whereas SELECT @foo = bar FROM MyVeryLargeTable will keep assigning the values until there are no more!
Kristen |
|
|
|
vivek.kumargupta
Starting Member
India
45 Posts |
Posted - 09/29/2005 : 01:56:44
|
yups ...
It can be handy at times and also SET is ANSI compliant
Thanks,
Vivek |
|
|
|
Golfnut_1969
Starting Member
USA
2 Posts |
Posted - 09/29/2006 : 15:26:46
|
quote:
Originally posted by vivek.kumargupta
So finally the conclusion is that SELECT is the right choice all the times be it performance or programming wise ...
But does that mean that SET was only created by MSFT to assign values to a single variable??I think MSFT must have done away with this SET at all!!!
:-)
-Vivek
|
|
|
|
Golfnut_1969
Starting Member
USA
2 Posts |
Posted - 09/29/2006 : 15:33:05
|
quote]Originally posted by vivek.kumargupta
So finally the conclusion is that SELECT is the right choice all the times be it performance or programming wise ...
But does that mean that SET was only created by MSFT to assign values to a single variable??I think MSFT must have done away with this SET at all!!!
:-)
-Vivek
[/quote]
Sorry, my original reply got lost.
I recommend using set simply because SELECT will sometime result in previous values. SELECT will not set a previous set variable's values to NULL if no value is returned in the SELECT statement. The SET Statement will. I have found this but in multiple places over the year and I have found that most people are not aware of it. Try the following SQL using the Northwind database. There are 9 records in the Employees table. I will loop through the records setting a variable equal to the first name. Then, once the value is set I will print the value. However, I will loop through the table 14 times, incrementing the EmployeeID each time. Once I get past 9 my select statement will not return a record yet the name value will still print the first name of Employee 9
USE Northwind
DECLARE @Counter int
DECLARE @EmployeeName varchar(10)
SET @Counter = 1
WHILE @Counter < 15 --There are 9 records in this table, EmployeeID 1-9
BEGIN
SELECT
@EmployeeName = FirstName
FROM Employees
WHERE EmployeeID = @Counter
PRINT '@EmployeeName = ' + IsNull(@EmployeeName,'NULL')
SET @Counter = @Counter + 1
END |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 09/29/2006 : 18:52:20
|
I don't see how SET helps there - the FirstName column might be NULL anyway! and thus not distinguishable from "missing" / "logic broken"
Personally I would check @@ROWCOUNT if my requirement was that a row existed in the resultset - and presumably the requirement would be for one row, in which case I would test that @@ROWCOUNT = 1
Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 09/30/2006 : 04:04:09
|
>>I recommend using set simply because SELECT will sometime result in previous values.
How?
Read my first reply
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Guennadi Vanine
Starting Member
5 Posts |
Posted - 05/02/2007 : 06:00:38
|
quote:
[i]Originally posted by Kristen
Personally I would check @@ROWCOUNT if my requirement was that a row existed in the resultset - and presumably the requirement would be for one row, in which case I would test that @@ROWCOUNT = 1
Inserting
print @@rowcount
before
END
results in
@EmployeeName = Nancy
1
@EmployeeName = Andrew
1
@EmployeeName = Janet
1
@EmployeeName = Margaret
1
@EmployeeName = Steven
1
@EmployeeName = Michael
1
@EmployeeName = Robert
1
@EmployeeName = Laura
1
@EmployeeName = Anne
1
@EmployeeName = Anne
1
@EmployeeName = Anne
1
@EmployeeName = Anne
1
@EmployeeName = Anne
1
@EmployeeName = Anne
1
So, checking @@rowcount is of no use |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5581 Posts |
Posted - 05/02/2007 : 06:11:24
|
Guennadi,
You need to check @@ROWCOUNT immediately after SELECT statement, not at the end of Loop:
DECLARE @Counter int
DECLARE @EmployeeName varchar(10)
SET @Counter = 1
WHILE @Counter < 15 --There are 9 records in this table, EmployeeID 1-9
BEGIN
SELECT
@EmployeeName = FirstName
FROM Employees
WHERE EmployeeID = @Counter
Print @@ROWCOUNT
PRINT '@EmployeeName = ' + IsNull(@EmployeeName,'NULL')
SET @Counter = @Counter + 1
END
So, checking @@ROWCOUNT is of the use !
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
Edited by - harsh_athalye on 05/02/2007 06:12:09 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
honigkuchenmann
Starting Member
1 Posts |
Posted - 08/10/2007 : 11:19:35
|
really nice to see the difference between SET and SELECT. especially that with nothing returned from the Select statement the previous value is kept..
USE Northwind
DECLARE @Counter AS int
DECLARE @EmployeeName AS varchar(10)
SET @Counter = 1
WHILE @Counter < 15 --There are 9 records in this table, EmployeeID 1-9
BEGIN
Select @EmployeeName = FirstName
FROM Employees
WHERE EmployeeID = @Counter
PRINT '@EmployeeName = ' + IsNull(@EmployeeName,'NULL')
SET @Counter = @Counter + 1
END
PRINT '------------Select vs. SET--------------------'
SET @Counter = 1
WHILE @Counter < 15 --There are 9 records in this table, EmployeeID 1-9
BEGIN
SET @EmployeeName = (Select FirstName
FROM Employees
WHERE EmployeeID = @Counter)
PRINT '@EmployeeName = ' + IsNull(@EmployeeName,'NULL')
SET @Counter = @Counter + 1
END
thanks Gulfnut!
|
|
|
| |
Topic |
|
SELECT vs SET