none
Problema na execução da Stored Procedure RRS feed

  • Pergunta

  • Bom dia pessoal, como vão?
    Estou com um problema quanto à criação de uma procedure.

    Esse é o script dela:

    USE [db]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE     XXXX
    AS
    BEGIN
    SET NOCOUNT ON

    set dateformat dmy

    declare @dataVisita varchar(10) = '08/05/2013'
    declare @codCliente int = 4

    select
     COUNT(v.codvisita) quantidade,
     isnull(t.descTipoVisita, 'Sem Tipo Visita') descTipoVisita,
     e.descEmpreendimento
    into #t1
    from
     tbVisita v left outer join tbTipoVisita t on v.codTipoVisita = t.codTipoVisita
     inner join tbEmpreendimento e on v.codEmpreendimento = e.codEmpreendimento
     inner join dbo.tbRelEmpreendimento_Regional empRel on e.codEmpreendimento = empRel.codEmpreendimento
    where
     dataVisita between @dataVisita + ' 00:00:00' and @dataVisita+' 23:59:59' and stsVisita = 'A'
    and
     ((@codCliente = 0) or (@codCliente = e.codCliente)
    and
     v.codTipoVisita <> 7 and v.stsVisita = 'A'
    and
     e.stsEmpreendimento = 'A'
    and
     e.bitSGP = 1
    group by t.descTipoVisita,descEmpreendimento


    DECLARE @cols AS VARCHAR(1000), @colsnull AS VARCHAR(1000), @query  AS VARCHAR(MAX), @query1  AS VARCHAR(1000), @query2  AS VARCHAR(100);

    set @cols = STUFF((SELECT distinct ',' + QUOTENAME(descTipoVisita) descTipoVisita
          FROM #t1 order by descTipoVisita
       FOR XML PATH(''), TYPE
       ).value('.', 'VARCHAR(1000)')
      ,1,1,'')

    set @colsnull = STUFF((SELECT distinct ',Isnull(' + QUOTENAME(descTipoVisita)+',0)' + QUOTENAME(descTipoVisita) descTipoVisita
          FROM #t1 order by descTipoVisita
       FOR XML PATH(''), TYPE
       ).value('.', 'VARCHAR(1000)')
      ,1,1,'')

    set @query = 'SELECT descEmpreendimento Empreendimento, '
    set @query = @query + @colsnull
    set @query = @query + ' from ( select descEmpreendimento, descTipoVisita, quantidade from #t1 ) x pivot ( MAX(quantidade) for descTipoVisita in ('
    set @query = @query + @cols
    set @query = @query + ')) p'
      
    exec(@query) 
     
    drop table #t1

    SET NOCOUNT OFF
    END

    Se eu executo apenas o sql funciona, mas se executo como uma procedure não. O problema esta no select. Tentei mesmo sem a tabela temporária e nada.

    Agradeceria se pudessem em ajudar.
    Grato.

    quinta-feira, 15 de agosto de 2013 15:22

Todas as Respostas

  • Olá,

    Qual mensagem de erro que retorna ao executar a procedure?

    Att,

    Roberto Galvão
    MCITP - Administration SQL Server 2008
    MCITP - Developer SQL Server 2008
    MCSA - SQL Server 2012


    Roberto Galvão | MCTS | MCITP | Microsoft Partner |

    quinta-feira, 15 de agosto de 2013 15:37
  • Não aparece erro apenas que executou e não retorna resultado nenhum.
    quinta-feira, 15 de agosto de 2013 15:39
  • Olá,

    Coloca tratamento nas colunas para valor nulo,  na sua concatenação das querys:

    set @cols = STUFF((SELECT distinct ',' + QUOTENAME(isnull(descTipoVisita, '')) descTipoVisita
          FROM #t1 order by descTipoVisita
       FOR XML PATH(''), TYPE
       ).value('.', 'VARCHAR(1000)') 
      ,1,1,'')

    set @colsnull = STUFF((SELECT distinct ',Isnull(' + QUOTENAME(descTipoVisita)+',0)' + QUOTENAME(isnull(descTipoVisita, '')) descTipoVisita
          FROM #t1 order by descTipoVisita
       FOR XML PATH(''), TYPE
       ).value('.', 'VARCHAR(1000)') 
      ,1,1,'')

    você deve estar concatenando algum valor com null que está invalidando sua query.

    se estiver usando sql 2012 vc pode usar o concat().

    Exemplo: http://bobgalvao.wordpress.com/2013/08/13/concatenacao-no-sql-server-2012-vs-concat/

    Roberto Galvão
    MCITP - Administration SQL Server 2008
    MCITP - Developer SQL Server 2008
    MCSA - SQL Server 2012


    Roberto Galvão | MCTS | MCITP | Microsoft Partner |


    quinta-feira, 15 de agosto de 2013 15:49
  • Eu estou usando o 2008 R2.

    Então, continua a mesma coisa.
    Caso estivesse concatenando com algum valor nulo não daria problema quando eu executasse só o script e não como procedure? Sem rodar como procedure funciona normalmente.

    quinta-feira, 15 de agosto de 2013 16:41
  • Percebi que falta um parenteses na linha 20 da proc:

    and e.bitSGP = 1) -- este aqui.
    group by t.descTipoVisita,descEmpreendimento

    Além disso, o retorno da variavel @query retorna com o comando completo? 

    quinta-feira, 15 de agosto de 2013 17:04
  • Opa,

    Faz o seguinte:

    Dá um print na variavel @query e vê qual é o retorno ao inves de exec(@query).

    PRINT @query

    outro ponto são as suas condições. 

    where 
     dataVisita between @dataVisita + ' 00:00:00' and @dataVisita+' 23:59:59' and stsVisita = 'A'
    and
     ((@codCliente = 0) or (@codCliente = e.codCliente)
    and
     v.codTipoVisita <> 7 and v.stsVisita = 'A'
    and
     e.stsEmpreendimento = 'A'
    and
     e.bitSGP = 1

    neste ponto parece está faltando um parêntesis:

    and  ( (@codCliente = 0) or (@codCliente = e.codCliente) )

    Espero te ajudado.

    Roberto Galvão
    MCITP - Administration SQL Server 2008
    MCITP - Developer SQL Server 2008
    MCSA - SQL Server 2012


    Roberto Galvão | MCTS | MCITP | Microsoft Partner |

    quinta-feira, 15 de agosto de 2013 17:12
  • Desculpe, mas foi erro meu na hora de copiar o scritp. O parênteses esta colocado.

    Sim Advaldo, retorna o comando completo.

    Roberto, mesmo o print não funciona se rodado como uma procedure, só se executando a query diretamente.

    quinta-feira, 15 de agosto de 2013 17:56
  • Opa,

    Faz assim, acho que não tem dados para as suas condições.

    antes de dar o DROP da tabela temporária

    faz um select nela para ver se nao esta vazia.

    select * from #t1

    drop table #t1

    outro ponto pode ser sua data:

    coloca assim:

    ( dataVisita >= cast(@dataVisita as datetime) and dataVisita < dateadd(day, 1, cast(@dataVisita as datetime)) )


    Roberto Galvão | MCTS | MCITP | Microsoft Partner |




    quinta-feira, 15 de agosto de 2013 18:04
  • Então, é exatamente isso.
    O primeiro select não esta consegindo inserir dados na tabela temporária. A mudança da condição da data deixou do mesmo modo.

    Os dados retornam dessa query executando diretamente, mas quando rodo chamando por procedure o primeiro select não retorna dados.

    Há algo que mude o comportamento de uma query numa procedure?

    quinta-feira, 15 de agosto de 2013 19:49
  • Bruno,

    Boa noite,

    Vamos tentar de outra forma e de forma parcial.

    vamos criar uma proc fazendo a operação de insert em uma variável table.

    Vê se os dados estão retornando certo. depois fazemos o outro passo da sua procedure.

    Script:

    CREATE PROCEDURE SPTESTE
    AS
    BEGIN
    declare @dataVisita varchar(10) = '2013-05-08'
    declare @codCliente int = 4

    -- VARIAVEL TABLE --
    DECLARE @TABLE TABLE (
    QUANTIDADE INT,
    DESCTIPOVISITA VARCHAR(250),
    DESCEMPREENDIMENTO VARCHAR(250)
    )
    INSERT INTO @TABLE
    select 
    COUNT(v.codvisita) quantidade
    , isnull(t.descTipoVisita, 'Sem Tipo Visita') descTipoVisita
    , e.descEmpreendimento
    from tbVisita v 
    left outer join tbTipoVisita t on v.codTipoVisita = t.codTipoVisita
    inner join tbEmpreendimento e on v.codEmpreendimento = e.codEmpreendimento
    inner join dbo.tbRelEmpreendimento_Regional empRel on e.codEmpreendimento = empRel.codEmpreendimento 
    where ( dataVisita >= cast(@dataVisita as datetime) and dataVisita < dateadd(day, 1, cast(@dataVisita as datetime)) )
    and ( (@codCliente = 0) or (@codCliente = e.codCliente) )
    and v.codTipoVisita <> 7 
    and v.stsVisita = 'A'
    and e.stsEmpreendimento = 'A'
    and e.bitSGP = 1
    group by  t.descTipoVisita
    , descEmpreendimento


    SELECT * FROM @TABLE 

    END
    GO

    Após o teste manda o feedback.

    Roberto Galvão
    MCITP - Administration SQL Server 2008
    MCITP - Developer SQL Server 2008
    MCSA - SQL Server 2012


    Roberto Galvão | MCTS | MCITP | Microsoft Partner |

    quinta-feira, 15 de agosto de 2013 22:20
  • Assim esta funcionando, mas apenas na aba da própria procedure. Se abro uma outra aba e mando executar não funciona.

    segunda-feira, 19 de agosto de 2013 15:31
  • Roberto, quando eu executo o sp_who2 active aparece essa linha:

    SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
    56    RUNNABLE                       sa PC149   . db SELECT INTO      234 32 08/19 12:52:28 Microsoft SQL Server Management Studio - Consulta 56    0   

    E toda vez que eu executo esse comando novamente atualiza a data e hora.

    segunda-feira, 19 de agosto de 2013 15:58
  • olá Bruno,

    A variável table é desprezada ao termino da execução.

    Cada vez que executares a procedure a variável é populada, retorna os dados no select feito e é finalizado.

    Se você executar assim na outra Aba:

    exec SPTESTE

    não funciona?

    Como os dados estão retornando com a variável table o próximo passo é arrumar seus Joins tirando a tabela temporária e colocar a variável table.

    DECLARE @cols AS VARCHAR(1000), @colsnull AS VARCHAR(1000), @query  AS VARCHAR(MAX), @query1  AS VARCHAR(1000), @query2  AS VARCHAR(100);

    set @cols = STUFF((SELECT distinct ',' + QUOTENAME(descTipoVisita) descTipoVisita
          FROM @TABLE order by descTipoVisita
       FOR XML PATH(''), TYPE
       ).value('.', 'VARCHAR(1000)') 
      ,1,1,'')

    set @colsnull = STUFF((SELECT distinct ',Isnull(' + QUOTENAME(descTipoVisita)+',0)' + QUOTENAME(descTipoVisita) descTipoVisita
          FROM @TABLE order by descTipoVisita
       FOR XML PATH(''), TYPE
       ).value('.', 'VARCHAR(1000)') 
      ,1,1,'')

    ....
    ....

    sp_executesql @query

    Espero ter ajudado.

    Roberto Galvão
    MCITP - Administration SQL Server 2008
    MCITP - Developer SQL Server 2008
    MCSA - SQL Server 2012


    Roberto Galvão | MCTS | MCITP | Microsoft Partner |

    segunda-feira, 19 de agosto de 2013 16:47