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

Question

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

Navigation

#1 by (3 votes)

2

Is it possible in the same script to create a temporary table from a run 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 throw the result of this execution into a temporary table, something like this:

    insert into #tab2
    EXECUTE SP_EXECUTESQL @SQLSTRING

to manipulate the data.

sql-server stored-procedures

asked by anonymous 14.06.2017 / 17:18

1 answer

CASE with IN does not work Doing searches and comparing java

score 3 · Accepted Answer

If you declare the temporary table in context outside the procedure, it is visible in the procedure and also in the dynamic SQL command executed within the procedure. You must run the INSERT inside the dynamic SQL command. This way, when the procedure is finished, the temporary table will be with the inserted information and can be manipulated.

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

ANSWERING THE ANSWER

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

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

Here is the code hint for getting 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 referential integers, 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