Diff between SET and SELECT
-
Hi
can anyone provide the difference between SET and SELECT used in assinging values to a local variable
Declare proc Test as
begin
declare @set int, @select int
select @select = 1
set @set = 1
what is the difference in the above two stmts and which one has to be used
end
-
To my knowledge both are same when used like
select @select = 1 or set @select = 1
But select can be used to get values from select statements, Set can just assign a value.
Ex set cannot be used here
SELECT @select = Colname FROM TABLENAME WHERE Col = value
or
SELECT @select = CASE WHEN GETDATE() < '01/01/2000' THEN 0 ELSE 1 END
Regards,
gova -
Hi !!!!!!!!!!
The main difference between SET and SELECT is that, you can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time.
Here's how:
/* Declaring variables */
DECLARE @Var1 AS int,
@Var2 AS int
/* Initializing two variables at once using SELECT */
SELECT @Var1 = 1, @Var2 = 2
/* Initializing two variables using SET */
SET @Var1 = 1
SET @Var2 = 2
If u want to maintan standards in T-SQL Programming, then you should be using SET. Because SET is the ANSI standard way of assigning values to variables, and SELECT is not.
And also: When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row.Make a note of this too....
But I am not sure if there is any performance difference between SET and SELECT statments.
Hope this helped u somewhere!
-
Nobody mentioned another important difference.
Compare:
Select @a = ColA from tblA where ColB = @b-2
Set @a = (select ColA from tblA where ColB = @b-2)
SET will fail if there are 2 or more rows where ColB = @B.
But SELECT will assign the last selected value from returned recordset and ignore the rest.
Sometimes it's a bug, but sometimes it's a really good feature.
But anyway you not gonna get SQL Server error using SELECT.
And by checking @@Rowcount after SELECT you may realize how many rows you've missed.
_____________
Code for TallyGenerator -
Another thing to look out for especially while looping (Obviously to be avoid whenever possible) but if you're using something along the lines of ...
SELECT @Variable = ColumnName FROM TABLE WHERE ID = @ID
And the Select statement doesn't return a value ie the ColumnName from the table is NULL or doesn't exist, using SELECT @Varaible will retain it's previous value (from the previous iteration of the loop) but if you were using ...
SET @Variable = (SELECT ColumnName FROM TABLE WHERE ID = @ID)
And the record couldn't be found @Variable will contain NULL rather that the previous value.
Another quite useful "feature" that is worth remembering.
-
dougjjj,
it may depend on some setting, but on my server what you say is true only if the SELECT statement does not return any row. If it does, and the column contains NULL, NULL is assigned to the variable. Anyway, this is quite serious difference between SET and SELECT, and something to be aware of...
-
Not sure on whether there is a setting for this or not but it only occurs when a row is not returned by the SELECT statement if a row is returned and the value is NULL the SELECT will hold the NULL, I wasn't as clear as I could have been in my previous post. Anyway definatly something worth knowing about, it's caused me countless hours of debuging import scripts until I picked up on this.
-
Paste the following into QA and click the "Display Estimated Execution Plan" tool button. Hang you mouse cursor over each "Select Cost 0%" page-image and you'll see that the "set" command is internally executed the same as a "select" command...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Ya, I know... here's the code...
Declare @v1 int,@v2 varchar(256)
Set @v1=123
Select @v1=456,@v2='789'
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
I believe that set is slower if you have multiple variables.
Profiler seems to show that the multiple SETs execute slower.
--Ric VA
Here is my example code.
If @test-2 = 0 then a Select is used
If @test-2 = 1 then multiple SETs are used
-----------------------
Declare @v1 int
, @v2 varchar(256)
, @v3 int
, @w int
, @test-2 int
-- change between 0 (use SELECT) and 1 (use SET)
SET @test-2 = 0
SET @w = 0
IF @test-2 = 0 --
BEGIN
WHILE (@w < 1000)
BEGIN
SELECT @w = @w + 1, @v1 = @w, @v2='This is my test', @v3 = @v1
END
END
ELSE
BEGIN
WHILE (@w < 1000)
BEGIN
SET @w = @w + 1
SET @v1 = @w
SET @v2 = 'This is my test'
SET @v3 = @v1
END
END
-
So long as select still works I'll use it (even though set is less to type !). The one advantage is the ability to set multiple values. The most obvious construct one I use quite often:
declare @rows_affected int
declare @error_code int
(execute some SQL statement)
Select @error_code=@@error, @rows_affected=@@rowcount
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Something else I didn't know about but found in ken henderson's book:
"You can't assign a cursor variable with a SELECT statement - only with SET.."
Also, to make the code more readable, what Henderson says makes perfect sense (as a general rule):
Use SET whenever you want to assign a value to a variable and SELECT when you want to return a result set!!!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply