SELECT within variable in PROCEDURE

Question

I’m not getting the result of a select within a variable.

I want to add the column turno inside my WHERE. The problem is 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 see that turno IN (@turnos) does not work, and so I tried to put the select all between quotation marks 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 1

You can use the SP_EXECUTESQL with a parameter OUTPUT to obtain 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;