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;