Interchanging of SET and SELECT commands.

Question

Firstly, I am under the impression that SET and SELECT are almost interchangeable. But when I changed the SELECT to SET in the following code, it cannot compile.

Can anyone explain to me why is this so ?

Thanks.

-- Create Procedure
CREATE PROCEDURE uspGetAddressCount @City nvarchar(30), @AddressCount int OUT
AS
SELECT @AddressCount = count(*) -- changing 'SELECT' to 'SET' doesn't work
FROM AdventureWorks.Person.Address 
WHERE City = @City

-- SQL query to call the procedure 
declare @AddressCount int  
exec uspGetAddressCount bothell, @AddressCount OUTPUT
select @AddressCount  -- Didn't know SELECT can be used to print values ?

"I am under the impression that SET and SELECT are almost interchangeable." They are not. Your impression is ill founded. — Steve Wellens, Mar 02 '14 at 04:57

score 1 · Answer 1 · edited May 23 '17 at 12:10

1

SET and SELECT are not Interchangeable.

Looks like this has already been discussed... SET vs. SELECT - What's the difference?

Again ... SET versus SELECT when assigning variables?

Also ... http://blog.sqlauthority.com/2007/04/27/sql-server-select-vs-set-performance-comparison/

Here too ... http://www.mssqltips.com/sqlservertip/1888/when-to-use-set-vs-select-when-assigning-values-to-variables-in-sql-server/

edited May 23 '17 at 12:10

Community

1
1

answered Mar 02 '14 at 04:57

sam yi

4,806
1
29
40

I read the first link before posting this. It stated that SET can only assign a scalar value. In this case, count(*) is a scalar value right ? Or is my understanding of scalar values wrong. Oops. – iridescent Mar 02 '14 at 05:12
1

I can see how that can be confusing but as you can see in the documentation for `SET`, you are not permitted to use it with `FROM`. http://technet.microsoft.com/en-us/library/ms189484.aspx – sam yi Mar 02 '14 at 05:24
The documentation didn't explicitly state it right ? =/ . So it must be inferred it is not permitted as all the examples shown use `SELECT` before a `FROM` statement ? The example `F. Assigning a value from a query` looks similar though. I tried putting brackets as well, didn't work out. – iridescent Mar 02 '14 at 06:26
I thought the documentation was quite explicit but if you feel you've been misled.. please ask Microsoft for a refund. ;) – sam yi Mar 03 '14 at 03:23
I am not saying you're wrong, I just want to know where did it state that `SET` cannot be used with `FROM`. Well, I might be really careless not to see it =/. – iridescent Mar 03 '14 at 05:53
In the `Syntax` section of that page, do a string search for the word "FROM", you'll notice it's not there. But in the end these are moot points... you just can't do what you were trying to do... properly documented or not. Hope that helps. – sam yi Mar 03 '14 at 21:36

score 0 · Answer 2 · answered Mar 02 '14 at 05:01

0

Set is used for updating the value of variable.. Select is for displaying the table based on conditions given

answered Mar 02 '14 at 05:01

user3256147

378
2
9

You can also "set" values to variable(s) using SELECT as well.. that's where the OP is getting confused. `SET @Variable = 1` and `SELECT @Variable = 1` are both perfectly legitimate. – sam yi Mar 02 '14 at 05:02

TTT · Answer 3 · 2014-05-29T19:23:55.597

Although the other answers are helpful, I don't think they fully answer the question. There is a syntactic difference with how SET and SELECT are used:

SELECT @AddressCount = count(*)
FROM AdventureWorks.Person.Address WHERE City = @City

becomes:

SET @AddressCount = (SELECT count(*)
FROM AdventureWorks.Person.Address WHERE City = @City)

Another important difference besides syntax is that if the query returns more than 1 row, then SET will throw an error, whereas SELECT will assign to the variable the value in the last row returned.

Interchanging of SET and SELECT commands.

3 Answers3