CODEWITHSUNDEEP
sqlsql-servert-sqlselectstored-procedures
iridescent
iridescent

Reputation: 305

Interchanging of SET and SELECT commands.

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 ?

Upvotes: 0

Views: 98

Answers (3)

TTT
TTT

Reputation: 29149

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.

Upvotes: 0

user3256147
user3256147

Reputation: 398

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

Upvotes: 0

sam yi
sam yi

Reputation: 4934

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/

Upvotes: 1

Related Questions