I've always had this doubt, but I've never researched it and even this happens a lot with me. In the StackOVerflow in English has this topic explaining when to use both the main differences are:
SET is ANSI standard for assigning variables, SELECT is not.
SET can only assign one variable at a time, SELECT can do several
assignments at the same time.
If you assign from a query, SET can only assign a value
climb. If the query returns multiple values / rows then SET
an error may occur. SELECT will assign one of the values to the variable e
will hide the fact that several values have been returned (then,
probably would never know why something was wrong in another
place)
When assigning from a query if there is no value
returned SET will assign NULL , where SELECT will not do the assignment
(so the variable will not be changed from its previous value). See code below
Regarding differences in performance there are no differences
between SET and SELECT . The ability to SELECT
making multiple assignments in a single action gives a slight advantage
performance on SET .
Translating code item 3:
Test yourself using the code below.
declare @var varchar(20)
set @var = 'Joe'
set @var = (select Campo from SuaTabela)
select @var
select @var = Campo from SuaTabela
select @var
The first code will return the following error message:
Subquery returned more than 1 value. This is not permitted when the
subquery follows =,! =, & lt ;, < =, & gt ;, > = or when the subquery is used as
an expression.
The second will return you a value.
Translating code item 4:
declare @var varchar(20)
set @var = 'Joe'
set @var = (select name from master.sys.tables where name = 'qwerty')
select @var /* @var Agora é null */
--SAÍDA NULL
set @var = 'Joe'
select @var = name from master.sys.tables where name = 'qwerty' -- AQUI NÃO É ATRIBUÍDO NULL
select @var
--SAÍDA Joe
It is very common to use the system in which I work SELECT in FUNCTIONS to concatenate comma-separated values instead of displaying one per line.
In practice
Imagine that I want to know all of your customers' emails, and they should have been comma-separated in a single result.
A FUNCTION that would solve your problem will look something like:
CREATE FUNCTION [dbo].[fncEmails](@IDCliente int)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @RETORNO VARCHAR(MAX)
SELECT @retorno = COALESCE(@retorno + ', ', '') + Email
FROM Clientes
RETURN @retorno
END
Result:
'[email protected], [email protected], [email protected]'
