sql – SELECT inside variable in PROCEDURE

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;
Scroll to Top