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
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?
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:
SETis ANSI standard for assigning variables,
SETcan only assign one variable at a time,
SELECTcan make multiple assignments at the same time.
- If assigning from a query,
SETcan only assign a scalar value. If the query returns multiple values/rows then
SETmight get an error.
SELECTassign 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)
- When assigning from a query if there is no value returned
SELECTwill not make the assignment (thus the variable will not be changed from its previous value). See code below
- As far as performance differences are concerned, there are no direct differences between
SELECT. SELECT's ability to do multiple assignments in a single action gives a slight performance advantage over
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
FUNCTIONS to concatenate comma-separated values instead of displaying one per line.
Imagine that I want to know all the emails from your customers and they must come separated by commas in a single result.
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
'firstname.lastname@example.org, email@example.com, firstname.lastname@example.org'