none
Formatação string com parâmetros RRS feed

  • Pergunta

  •  Colegas boa tarde,

     Tento passar uma ação por parâmetro mas, não esta dando certo,  tentei de varias formas  não consegui

     abaixo segue o que tento fazer. 

     Procedure  XXXXXXXX

    Parametro :   ,@condicao as varchar(7) = 'NOT In'  

    Parque estou tentando aplicar:  

           where cp.cod_cliente  ''' +  @condicao + '''  isnull(@cnpj, cp.cod_cliente)     
      and data_movimento between @data_inicio and @data_fim     
      and a.autorizacao like '%' + isnull(@nsu, a.autorizacao) + '%'  

      

      Fianlizade é usar as condições de IN  e  NOT In

     exec dbo.prc_rel_transacoes_detalhadas '2019-08-20','2019-11-22', 'IN' ,'99999999999999' 

    OU

    exec dbo.prc_rel_transacoes_detalhadas '2019-08-20','2019-11-22', 'Not IN' ,'99999999999999' 

    Tentei de varias formas  usando,  ''' = '''   ou ''" +   "''   não obtive resultado.

    Peço um apoio  

    Obrigado..


    sexta-feira, 22 de novembro de 2019 17:39

Respostas

  • Colegas,

    Obrigado pelo apoio.

    Resolvi fazer dois procedimentos um para =  outro para != , 

    penso que é uma maneira mais rápida de solucionar  o problema da emissão dos relatórios.

    Uma vez implantado e dando o resultado esperado, terei mais tempo para achar uma solução

    para o caso  em questão. 

    Deixei desta  forma:

    procedimento1 -  where cp.cod_cliente  != isnull(@cnpj ,cp.cod_cliente)   rodo desta forma

    procedimento2 - where cp.cod_cliente   = isnull(@cnpj ,cp.cod_cliente)  outro desta

    Obrigado  Colegas.



    segunda-feira, 25 de novembro de 2019 15:31

Todas as Respostas

  • Antônio,

    Você esta tentando passar este parâmetro em uma query dinâmica no Management Studio ou através de alguma aplicação?

    Poderia nos detalhar um pouco mais como você esta implementando este código.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 22 de novembro de 2019 19:54
  • Boa tarde Junior, 

       Pretendo passar esse parâmetro através de uma  procedure.

    A minha dúvida é se consigo passar  uma instrução através de um parâmetro..

     Parte da Procedure.

     ,@condicao as varchar(7) = 'NOT In'    
     ,@cnpj as varchar(14) = null    
     ,@nsu as varchar(20) = null    
     ,@data_recebimento_inicio as date = '1900-01-01'    
     ,@data_recebimento_fim as date = '2900-12-31'    
     ,@tipo_relatorio as int = 1    
     ,@is_antecipado as int = null    
    AS    
    begin    
     if(@tipo_relatorio = 1)    
     begin    
      select DATEDIFF(day, data_movimento, getdate()) AS id, cp.cod_cliente,conf.nome_fantasia,  convert(char(10), data_movimento,103) as 'data_movimento'  
      , convert(char(10), disponivel_para_casa_caixa_em,103) as 'disponivel_para_casa_caixa_em',   
      case isnull(data_antecipacao_casa, 0) when 0 then 'NÃO' else 'SIM' end as antecipado, data_antecipacao_casa  
      ,  r.descricao_tipo_recebimento, r.credito_debito, a.autorizacao as nsu,    
      valor_bruto_casa, (valor_bruto_casa - valor_liquido_operadora_casa) as desconto_operadora, valor_liquido_operadora_casa,    
      (valor_liquido_operadora_casa - valor_liquido_casa) as desconto_icon, valor_liquido_casa,     
      case (DATEDIFF(month, dbo.get_data_caixa_fluxo(data_movimento, 'F', 1), disponivel_para_casa_caixa_em))   
      when 1 then case(credito_debito) when 'C' then 1 else 0 end else DATEDIFF(month, dbo.get_data_caixa_fluxo(data_movimento, 'F', 1), disponivel_para_casa_caixa_em) end as numero_parcela, a.equipamento as terminal    
     from comandas_faturamento_servicos_pagamento (nolock) cp     
     inner join tipo_recebimento_base (nolock) r on cp.cod_tipo_recebimento = r.cod_tipo_recebimento     
     inner join autorizacoes_parceladas (nolock) ap     
     inner join autorizacoes a    
     inner join estabelecimento_cliente (nolock) ec on a.cod_estabelecimento = ec.cod_estabelecimento     
        inner join configuracao (nolock) conf on conf.cnpj = ec.cod_cliente      
      on a.autorizacao = ap.autorizacao and rtrim(ltrim(a.cod_estabelecimento)) = rtrim(ltrim(ap.cod_estabelecimento))   
      and ap.cod_tipo_recebimento_icon = a.cod_tipo_recebimento_icon    
      on cp.data_movimento =  ap.data_transacao and cp.cod_tipo_recebimento = ap.cod_tipo_recebimento_icon   
      and cp.cod_cliente = ec.cod_cliente   
      and cp.cod_carteirinha = a.cod_carteirinha   
      and cp.n_item = a.n_item and cp.valor_bruto_casa = ap.valor_a_receber and  
     data_previsao_recebimento = ap.data_recebimento    
      where cp.cod_cliente  ''' +  @condicao + '''  isnull(@cnpj, cp.cod_cliente)   ..............

     

     @condicao   dever ser igual 'NOI IN'  ou 'IN'

     Não conseguir definir a formatação  correta que reconheça  o conteúdo deste parametro.

    exec dbo.prc_rel_transacoes_detalhadas '2019-08-20','2019-11-22', 'IN' ,'99999999999999' 

    OU

    exec dbo.prc_rel_transacoes_detalhadas '2019-08-20','2019-11-22', 'Not IN' ,'99999999999999' 

    sexta-feira, 22 de novembro de 2019 20:30
  • Antonio, não é possível definir o operador em tempo de execução. A construção
           where cp.cod_cliente  +  @condicao + isnull(@cnpj, cp.cod_cliente)

    não é válida para um código SQL estático. Isto é, o operador deve ser conhecido no momento em que o código fonte da consulta é analisado.

    Além disso, as construções
          cp.cod_cliente  in  isnull(@cnpj, cp.cod_cliente)
    e
          cp.cod_cliente  not in  isnull(@cnpj, cp.cod_cliente)

    não são válidas.

    ---

    O parâmetro @cnpj está definido com tamanho de 14 caracteres; ou seja, somente é possível informar um e único CNPJ. Sendo assim, me parece que não faz sentido o uso do operador IN / NOT IN. Talvez o que queira seja algo como "=" (igual) e "<>" (diferente); é isto?

    Se explicar qual é o objetivo do filtro pode facilitar o envio de sugestões; às vezes a solução é algo completamente diferente.

     

    Lembre-se de marcar esta resposta se ela te ajudou a resolver o problema


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz sexta-feira, 22 de novembro de 2019 21:49
    sexta-feira, 22 de novembro de 2019 21:22
  • Você comentou que tentou o operador "=".

    Considerando-se o uso de "=" ou "<>" como valores para o parâmetro @condicao, avalie substituir

          where cp.cod_cliente + @condicao + isnull(@cnpj, cp.cod_cliente)

    por

    -- código #1
    ... where (case when @condicao = '=' then case when (@cnpj is null or cod_cliente = @cnpj) then 1 else 0 end when @condicao = '<>' then case when (@cnpj is null or cod_cliente <> @cnpj) then 1 else 0 end end) = 1
    and ...
     

    Lembre-se de marcar esta resposta se ela te ajudou a resolver o problema


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz sexta-feira, 22 de novembro de 2019 22:36
    sexta-feira, 22 de novembro de 2019 21:49
  • (...) existe um único CNJP  que tenho que usar como filtro  ou seja  , são dois tipos de relatórios  um com cnpj especifico  e o outros com todos que forem diferente deste.

    Neste caso você pode utilizar o filtro montado no código #1, não sendo necessário construir dois procedimentos. No parâmetro @condicao informe = para o relatório com cnpj específico e <> para o relatório com "todos que forem diferente deste".

     


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz sexta-feira, 22 de novembro de 2019 22:40
    sexta-feira, 22 de novembro de 2019 22:38
  • José,  tive uma evolução segundo a sua sugestão , porém , terei que descobri o porque  do estouro  de uma variável.

    Fiz da seguinte forma:  

                                                                         

    data_previsao_recebimento = ap.data_recebimento    
       where (case when @condicao = '=' 
                      then     cp.cod_cliente + ''' = ''' + isnull(@cnpj ,cp.cod_cliente)

                  when @condicao = '<>' 
                      then     cp.cod_cliente + ''' != ''' + isnull(@cnpj ,cp.cod_cliente)

            end) = 1
       and data_movimento between @data_inicio and @data_fim 

     Deu o seguinte erro:  

    Msg 248, Nível 16, Estado 1, Procedimento dbo.prc_rel_transacoes_detalhadas, Linha 16 [Linha de Início do Lote 1]
    The conversion of the nvarchar value '02832754000133' != '07448732000198' overflowed an int column.

    Perceba que supostamente montou a string certa,  operador com sinal de diferente e os valor entre aspas simples

    Execução: exec dbo.prc_rel_transacoes_detalhadas '2019-08-20','2019-11-22', '<>'  ,'07448732000198'


    Obrigado.

    sexta-feira, 22 de novembro de 2019 23:19
  • Antonio, a construção
         then     cp.cod_cliente + ''' != ''' + isnull(@cnpj ,cp.cod_cliente)

    está juntando o conteúdo da coluna cp.cod_cliente (02832754000133) com ' != ' e com o conteúdo de @cnpj (07448732000198). Ou seja, criou um texto com o seguinte conteúdo:
        02832754000133' != '07448732000198

    Pela mensagem de erro, o resultado dessa expressão é do tipo nvarchar. Após, houve a comparação desse texto com o valor 1 (que é int). E aí caiu-se no problema da conversão implícita, conforme consta no artigo "Os perigos da conversão implícita".

    Como @condicao e @cnpj são parâmetros do procedimento, sugiro que implemente sem alterações o filtro que consta no código #1.

    Lembre-se de marcar esta resposta se ela te ajudou a resolver o problema


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz sábado, 23 de novembro de 2019 17:30
    sexta-feira, 22 de novembro de 2019 23:39
  • O erro ocorreu ao tentar converter
        02832754000133' != '07448732000198

    para o tipo de dados int.

    Eis simulação do que ocorreu:

    -- código #2
    declare @cod_cliente varchar(14), @cnpj varchar(14);
    
    set @cod_cliente= '02832754000133';
    set @cnpj= '07448732000198';
    
    SELECT @cod_cliente + ''' != ''' + isnull(@cnpj, @cod_cliente);
    
    SELECT cast (@cod_cliente + ''' != ''' + isnull(@cnpj, @cod_cliente) as int);

    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    sábado, 23 de novembro de 2019 00:34
  • Boa tarde Junior, 

       Pretendo passar esse parâmetro através de uma  procedure.

    A minha dúvida é se consigo passar  uma instrução através de um parâmetro..

     Parte da Procedure.

     ,@condicao as varchar(7) = 'NOT In'    
     ,@cnpj as varchar(14) = null    
     ,@nsu as varchar(20) = null    
     ,@data_recebimento_inicio as date = '1900-01-01'    
     ,@data_recebimento_fim as date = '2900-12-31'    
     ,@tipo_relatorio as int = 1    
     ,@is_antecipado as int = null    
    AS    
    begin    
     if(@tipo_relatorio = 1)    
     begin    
      select DATEDIFF(day, data_movimento, getdate()) AS id, cp.cod_cliente,conf.nome_fantasia,  convert(char(10), data_movimento,103) as 'data_movimento'  
      , convert(char(10), disponivel_para_casa_caixa_em,103) as 'disponivel_para_casa_caixa_em',   
      case isnull(data_antecipacao_casa, 0) when 0 then 'NÃO' else 'SIM' end as antecipado, data_antecipacao_casa  
      ,  r.descricao_tipo_recebimento, r.credito_debito, a.autorizacao as nsu,    
      valor_bruto_casa, (valor_bruto_casa - valor_liquido_operadora_casa) as desconto_operadora, valor_liquido_operadora_casa,    
      (valor_liquido_operadora_casa - valor_liquido_casa) as desconto_icon, valor_liquido_casa,     
      case (DATEDIFF(month, dbo.get_data_caixa_fluxo(data_movimento, 'F', 1), disponivel_para_casa_caixa_em))   
      when 1 then case(credito_debito) when 'C' then 1 else 0 end else DATEDIFF(month, dbo.get_data_caixa_fluxo(data_movimento, 'F', 1), disponivel_para_casa_caixa_em) end as numero_parcela, a.equipamento as terminal    
     from comandas_faturamento_servicos_pagamento (nolock) cp     
     inner join tipo_recebimento_base (nolock) r on cp.cod_tipo_recebimento = r.cod_tipo_recebimento     
     inner join autorizacoes_parceladas (nolock) ap     
     inner join autorizacoes a    
     inner join estabelecimento_cliente (nolock) ec on a.cod_estabelecimento = ec.cod_estabelecimento     
        inner join configuracao (nolock) conf on conf.cnpj = ec.cod_cliente      
      on a.autorizacao = ap.autorizacao and rtrim(ltrim(a.cod_estabelecimento)) = rtrim(ltrim(ap.cod_estabelecimento))   
      and ap.cod_tipo_recebimento_icon = a.cod_tipo_recebimento_icon    
      on cp.data_movimento =  ap.data_transacao and cp.cod_tipo_recebimento = ap.cod_tipo_recebimento_icon   
      and cp.cod_cliente = ec.cod_cliente   
      and cp.cod_carteirinha = a.cod_carteirinha   
      and cp.n_item = a.n_item and cp.valor_bruto_casa = ap.valor_a_receber and  
     data_previsao_recebimento = ap.data_recebimento    
      where cp.cod_cliente  ''' +  @condicao + '''  isnull(@cnpj, cp.cod_cliente)   ..............

     

     @condicao   dever ser igual 'NOI IN'  ou 'IN'

     Não conseguir definir a formatação  correta que reconheça  o conteúdo deste parametro.

    exec dbo.prc_rel_transacoes_detalhadas '2019-08-20','2019-11-22', 'IN' ,'99999999999999' 

    OU

    exec dbo.prc_rel_transacoes_detalhadas '2019-08-20','2019-11-22', 'Not IN' ,'99999999999999' 

    Antonio,

    Da forma que você esta pensando, tendo uma estrutura estática ou fixa não, teríamos que tratar a própria Stored Procedure como um recurso dinâmico para ser executada através do comando SP_ExecuteSQL em conjunto com os parâmetros, sejam eles de entrada ou saída.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 23 de novembro de 2019 15:10
  • José,  tive uma evolução segundo a sua sugestão , porém , terei que descobri o porque  do estouro  de uma variável.

    Fiz da seguinte forma:  

                                                                         

    data_previsao_recebimento = ap.data_recebimento    
       where (case when @condicao = '=' 
                      then     cp.cod_cliente + ''' = ''' + isnull(@cnpj ,cp.cod_cliente)

                  when @condicao = '<>' 
                      then     cp.cod_cliente + ''' != ''' + isnull(@cnpj ,cp.cod_cliente)

            end) = 1
       and data_movimento between @data_inicio and @data_fim 

     Deu o seguinte erro:  

    Msg 248, Nível 16, Estado 1, Procedimento dbo.prc_rel_transacoes_detalhadas, Linha 16 [Linha de Início do Lote 1]
    The conversion of the nvarchar value '02832754000133' != '07448732000198' overflowed an int column.

    Perceba que supostamente montou a string certa,  operador com sinal de diferente e os valor entre aspas simples

    Execução: exec dbo.prc_rel_transacoes_detalhadas '2019-08-20','2019-11-22', '<>'  ,'07448732000198'


    Obrigado.

    Antonio,

    Neste caso, você vai ter que tratar ambos os valores como elementos separados, para que seja possível estabelecer o filtro de pesquisa de dados.



    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 23 de novembro de 2019 15:13
  • Colegas,

    Obrigado pelo apoio.

    Resolvi fazer dois procedimentos um para =  outro para != , 

    penso que é uma maneira mais rápida de solucionar  o problema da emissão dos relatórios.

    Uma vez implantado e dando o resultado esperado, terei mais tempo para achar uma solução

    para o caso  em questão. 

    Deixei desta  forma:

    procedimento1 -  where cp.cod_cliente  != isnull(@cnpj ,cp.cod_cliente)   rodo desta forma

    procedimento2 - where cp.cod_cliente   = isnull(@cnpj ,cp.cod_cliente)  outro desta

    Obrigado  Colegas.



    segunda-feira, 25 de novembro de 2019 15:31