How to assign a EXECUTE SP_EXECUTESQL @SQLSTRING to a temporary table

Question

It is possible in the same script to create a temporary table from an execution like this?

        --****************************EMPRESA******************************
 if(Object_id('tempdb..#TBEmpresa') is not null)
    drop table #TBEmpresa
go  
 CREATE TABLE #TBEmpresa(
 EmpresaID int null,
 Empresa   varchar(50) null
 )
 go 
 insert into #TBEmpresa (EmpresaID, Empresa) values (1,'Vivo')
 insert into #TBEmpresa (EmpresaID, Empresa) values (2,'TIM')
 insert into #TBEmpresa (EmpresaID, Empresa) values (3,'Claro')
 go

 --*****************************SERVICOS***************************
if(OBJECT_ID('tempdb..#TBServico') is not null)
    drop table #TBServico
go
create table #TBServico(
ServicoID        int null,
DescricaoServico varchar(50) null
)
go

insert into #TBServico (ServicoID, DescricaoServico) values (1, 'Serviço de Dados')
insert into #TBServico (ServicoID, DescricaoServico) values (2, 'Serviço de TV')
insert into #TBServico (ServicoID, DescricaoServico) values (3, 'Servico de Internet')
insert into #TBServico (ServicoID, DescricaoServico) values (4, 'Servico de Voz')
insert into #TBServico (ServicoID, DescricaoServico) values (5, 'Servico de PABX')
go

--**********************SERVIÇO X EMPRESA *******************************
if(OBJECT_ID('tempdb..#TBServicoEmpresa') is not null)
    drop table #TBServicoEmpresa
go
create table #TBServicoEmpresa(
TBServicoEmpresaID int null,
TBEmpresaId        int null,
TBServicoId        int null
)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 1,1,1)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 2,1,2)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 3,1,3)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 4,1,4)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 5,1,5)

insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 6,2,1)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 7,2,2)

insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 8,2,3)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 9,2,4)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values (10,2,5)

--**********************PROCESSAMENTO************************************
if (OBJECT_ID('tempdb..#TBProcessamento') is not null)
    drop table #TBProcessamento
go
create table #TBProcessamento(
ProcessamentoID int null,
DataProcesso    DateTime null
)
go
insert into #TBProcessamento (ProcessamentoID, DataProcesso) values (1, '2017-06-22 00:00:00.000')
insert into #TBProcessamento (ProcessamentoID, DataProcesso) values (2, '2017-06-23 00:00:00.000')
insert into #TBProcessamento (ProcessamentoID, DataProcesso) values (3, '2017-06-24 00:00:00.000')
--***********************GRADE********************************************************************
if(OBJECT_ID('tempdb..#TBGrade') is not null)
    drop table #TBGrade
go
create table #TBgrade(
GradeId         int null,
EmpresaId       int null,
ServicoId       int null,
ProcessamentoId int null,
Data            DateTime null,
Situacao        varchar(50) null
)
go
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 1,1,1,1, 'EM USO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 2,1,1,2, 'DESCONTINUADO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 3,1,1,3, 'EM PROCESSO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 4,1,1,4, 'EM USO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 5,1,1,5, 'EM USO', '2017-06-22 00:00:00.000')

insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 6,1,2,1, 'DESCONTINUADO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 7,1,2,2, 'EM USO', '2017-06-22 00:00:00.000')

insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 8,1,3,3, 'DESCONTINUADO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 9,1,3,4, 'EM PROCESSO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values (10,1,3,5, 'EM PROCESSO', '2017-06-22 00:00:00.000')
go
---*************************************************************************************************************************************

if(OBJECT_ID('tempdb..#TBResultado') is not null)
    drop table #TBResultado
go

select GRD.ProcessamentoId, EMP.EmpresaID, EMP.Empresa, SRV.ServicoID, SRV.DescricaoServico, GRD.Situacao, GRD.Data
into #TBResultado
from #TBEmpresa as EMP
    inner join #TBgrade as GRD
        on GRD.EmpresaID = EMP.EmpresaID
    inner join #TBServicoEmpresa as SRVEMP
        on GRD.EmpresaId = SRVEMP.TBEmpresaId
    inner join #TBServico as SRV
        on SRV.ServicoID = GRD.ServicoId
GROUP BY GRD.ProcessamentoId, EMP.EmpresaID, EMP.Empresa, SRV.ServicoID, SRV.DescricaoServico, GRD.Situacao, GRD.Data

DECLARE @COLUNAS VARCHAR(MAX)
SET @COLUNAS = ''
SELECT @COLUNAS = COALESCE(@COLUNAS + '[' + (CAST(DescricaoServico AS NVARCHAR(255))) + '],','')
FROM (SELECT DISTINCT DescricaoServico FROM #TBResultado) AS DADOS_HORIZONTAIS

SET @COLUNAS = LEFT (@COLUNAS, LEN(@COLUNAS)-1)
DECLARE @SQLSTRING NVARCHAR(1200);

SET @SQLSTRING = N'SELECT * FROM('+
                                'SELECT '+
                                'ProcessamentoId, '+
                                'EmpresaID, '+
                                'Empresa, '+
                                'ServicoID, '+
                                'DescricaoServico, '+
                                'Situacao,  '+
                                'Convert(varchar(10), Data,103) as [DATA] '+
                                'FROM #TBResultado) AS DADOS_HORIZONTAIS '+
                'PIVOT(MAX(Situacao) FOR DescricaoServico IN('+@COLUNAS+')) AS PivotTable '+
                'ORDER BY DATA DESC;'

EXECUTE SP_EXECUTESQL @SQLSTRING

I need to play the result of this execution on a temporary table, something like that:

    insert into #tab2
    EXECUTE SP_EXECUTESQL @SQLSTRING

to manipulate the data.

Answer 1

If you declare the temporary table in context external to the procedure, it is visible in the procedure and also in the dynamic SQL command executed within the procedure. You must run INSERT within the dynamic SQL command. This way, at the end of the execution of the procedure, the temporary table will be with the information inserted and can be manipulated.

The detailed explanation you find in chapter 9 of the book Inside Microsoft SQL Server 2008: T-SQL Programming.

---

EXTENSION OF THE ANSWER

I need to play the result of this execution on a temporary table, something like that:
insert into #tab2
EXECUTE SP_EXECUTESQL @SQLSTRING
to manipulate the data.

As commented in the initial text of this reply, If you declare the temporary table in context external to the procedure, it is visible in the procedure and also in the dynamic SQL command executed within the procedure.. That is, just create the #Tbresult temporary table before running the dynamic SQL command. However, after analyzing the code later added by the author of the topic, it is noticed that the complete structure of the temporary table is not known previously, possessing variable column number (it depends on the number of rows in the Tbservico table). To circumvent this fact, the basic structure of the #Tbresult table is created using static SQL command and, through dynamic SQL command, the variable columns are added.

Here is suggested code to get the report.

-- código #1 v3

--==========
 -- cria a tabela temporária para armazenar resultado da consulta dinâmica
IF Object_ID('tempDB..#TBResultado', 'U') is not null
  DROP TABLE #TBResultado;

CREATE TABLE #TBResultado (
   ProcessamentoID int not null,
   DataProc date not null,
   EmpresaID int not null,
   Empresa varchar(50) not null
);

-- monta nome das colunas para a consulta e comando de alteração da tabela #TBResultado
declare @comandoSQL varchar(8000);
set @comandoSQL= '';

declare @colID varchar(200), @colDescID varchar(4000);
set @colID= '';
set @colDescID= '';

SELECT @colID+= '[' + cast(ServicoID as varchar(3)) + '],',
       @colDescID+= '[' + cast(ServicoID as varchar(3)) + '] as [' + 
                     cast(ServicoID as varchar(3)) + ' - ' + DescricaoServico + '], ',
       @comandoSQL+= 
          'ALTER TABLE #TBResultado ADD [' + 
          cast(ServicoID as varchar(3)) + ' - ' + 
          DescricaoServico + '] varchar(50); '
  from TBServico
  order by DescricaoServico;
set @colID= left(@colID, len(@colID)-1);
set @colDescID= left(@colDescID, len(@colDescID)-1);  

-- acrescenta, usando comando SQL dinâmico, as colunas de serviço
--PRINT @comandoSQL;
EXECUTE (@comandoSQL);

-- monta comando SQL dinâmico
set @comandoSQL= 
  'with cteResultado as ( ' +
  'SELECT GRD.ProcessamentoId, GRD.Data, SRVEMP.EmpresaID, SRVEMP.ServicoID, GRD.Situacao ' +
  'from TBgrade as GRD ' +
  '    inner join TBServicoEmpresa as SRVEMP on SRVEMP.ServicoEmpresaId = GRD.ServicoEmpresaId ' +
  '    inner join TBProcessamento as PRC on PRC.ProcessamentoId = GRD.ProcessamentoId ' +
  '), ' +
  'cteP as ( ' +
  'SELECT ProcessamentoID, Data, EmpresaID, ' + @colID +
  '  from cteResultado ' +
  '       pivot (max(Situacao) for ServicoID in (' + @colID + ')) as P ' +
  ') ' +
  'INSERT into #TBResultado ' +
  '  SELECT P.ProcessamentoID, P.Data, ' +
  '         P.EmpresaID, EMP.Empresa, ' + @colID +
  '    from cteP as P ' +
  '      inner join TBEmpresa as EMP on EMP.EmpresaId = P.EmpresaId;';

-- executa a consulta via comando SQL dinâmico
--PRINT @comandoSQL
EXECUTE (@comandoSQL)

-- exibe conteúdo da tabela #TBResultado
SELECT *
  from #TBResultado;

---

Note that the declaration of the tables and the load commands have been revised, with the addition of reference integrities, primary keys etc.

-- código #2
set nocount on

if OBJECT_ID('TBGrade') is not null
    drop table TBGrade;

if OBJECT_ID('TBServicoEmpresa', 'U') is not null
    drop table TBServicoEmpresa;

if Object_id('TBEmpresa') is not null
    drop table TBEmpresa;

if Object_id('TBServico') is not null
    drop table TBServico;

if (OBJECT_ID('TBProcessamento') is not null)
    drop table TBProcessamento;
go

       --****************************EMPRESA******************************
 CREATE TABLE TBEmpresa(
    EmpresaID int not null primary key,
    Empresa   varchar(50) not null
 )
 go 
 insert into TBEmpresa (EmpresaID, Empresa) values 
    (1,'Vivo'), (2,'TIM'), (3,'Claro');
 go

 --*****************************SERVICOS***************************
create table TBServico(
   ServicoID        int not null primary key,
   DescricaoServico varchar(50) not null
)
go

insert into TBServico (ServicoID, DescricaoServico) values 
   (1, 'Serviço de Dados'),
   (2, 'Serviço de TV'),
   (3, 'Servico de Internet'),
   (4, 'Servico de Voz'),
   (5, 'Servico de PABX');
go

--**********************SERVIÇO X EMPRESA *******************************
create table TBServicoEmpresa(
   ServicoEmpresaID int not null primary key,
   EmpresaId        int not null references TBEmpresa,
   ServicoId        int not null references TBServico
);

insert into TBServicoEmpresa (ServicoEmpresaID, EmpresaId, ServicoId) values 
  (1,1,1), (2,1,2), (3,1,3), (4,1,4), (5,1,5), (6,2,1),
  (7,2,2), (8,2,3), (9,2,4), (10,2,5);


  --**********************PROCESSAMENTO************************************
create table TBProcessamento(
   ProcessamentoID int not null primary key,
   DataProcesso    Date not null
);
go

set dateformat ymd;
insert into TBProcessamento (ProcessamentoID, DataProcesso) values 
     (1, '2017-06-22'),
     (2, '2017-06-23'),
     (3, '2017-06-24');


--***********************GRADE********************************************************************

create table TBgrade(
   GradeId         int not null primary key,
   ServicoEmpresaId int not null references TBServicoEmpresa,
   ProcessamentoId int not null references TBProcessamento,
   Data            Date not null,
   Situacao        varchar(50) not null
);
go

insert into TBGrade (GradeId, ServicoEmpresaId, ProcessamentoId, Situacao, Data) values 
   (1,1,1, 'EM USO', '2017-06-22'),
   (2,2,1, 'DESCONTINUADO', '2017-06-22'),
   (3,3,1, 'EM PROCESSO', '2017-06-22'),
   (4,4,1, 'EM USO', '2017-06-22'),
   (5,5,1, 'EM USO', '2017-06-22'),
   (6,6,1, 'DESCONTINUADO', '2017-06-22'),
   (7,7,1, 'EM USO', '2017-06-22'),
   (8,8,1, 'DESCONTINUADO', '2017-06-22'),
   (9,9,1, 'EM PROCESSO', '2017-06-22'),
   (10,10,1, 'EM PROCESSO', '2017-06-22');   
go