sql – When to use SET and SELECT?

Question:

I know that as in practically all questions related to " one or the other " there must be situations in which it is better to use SET or SELECT in a procedure (for example).

At work we have the habit of always using SELECT , even to assign a value to only one variable, eg:

@declare @variavel varchar(2)
select @variavel = 'ok'

Is this the best practice or because it is a simple assignment would it be better to use SET ? Are there any performance/memory advantages to using SELECT in situations like this?

Answer:

I always had this doubt, but I never researched it and even this happens a lot to me. On StackOVERflow in English there is this topic explaining when to use one and the other, the main differences are:

  1. SET is ANSI standard for assigning variables, SELECT is not.
  2. SET can only assign one variable at a time, SELECT can make multiple assignments at the same time.
  3. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET might get an error. SELECT assign one of the values ​​to the variable and hide the fact that multiple values ​​were returned (so you would probably never know why something was wrong elsewhere)
  4. When assigning from a query if there is no value returned SET assign NULL , where SELECT will not make the assignment (thus the variable will not be changed from its previous value). See code below
  5. As far as performance differences are concerned, there are no direct differences between SET and SELECT . SELECT's ability to do multiple assignments in a single action gives a slight performance advantage over SET .

Translating into 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 =, !=, <, <= , >, >= or when the subquery is used as an expression.

The second will return you a value.

Translating into 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 quite common to use in the system where I work SELECT on FUNCTIONS to concatenate comma-separated values ​​instead of displaying one per line.

In practice

Imagine that I want to know all the emails from your customers and they must come separated by commas in a single result.

A FUNCTION that would solve your problem will look 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:

'marconimbarroso@gmail.com, teste@gmail.com, alguem@outlook.com'

SQLFiddle

Post Views: 1
Scroll to Top