Question:
I'm not able to put the result of a select inside a variable.
I want to add the turno column inside my WHERE . The problem is that I get the value of @turnos in the following 3 formats:
1 –
'A'2 –
'A','B'3 –
'A','B','C'
Current situation
set @tjt = (select sum(duracao) from tabela where fabrica = @fabrica)
Desired situation (with error)
set @tjt= (select sum(duracao) from tabela where fabrica = @fabrica and turno IN (@turnos))
I've seen that turno IN (@turnos) doesn't work, so I tried to put the entire select in quotes concatenating the variables as follows:
@query = '(select sum(duracao) from tabela where fabrica = '+@fabrica+'
and turno IN ('+@turnos+'))'
But I still don't understand how to put the result of this @query in the variable @tjt .
Answer:
You can use SP_EXECUTESQL with an OUTPUT parameter to get the desired result:
DECLARE @tjt INT;
DECLARE @query NVARCHAR(MAX);
SET @query = '(select @tjt = sum(duracao) from tabela where fabrica = ' + @fabrica + ' and turno IN (' + @turnos + '))';
EXEC SP_EXECUTESQL @query, N'@tjt INT OUT', @tjt OUT;
SELECT @tjt;