Usuário com melhor resposta
Formatação string com parâmetros

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..
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.
- Editado Antonio Luiz Tadeu segunda-feira, 25 de novembro de 2019 15:33 Grafia
- Sugerido como Resposta Junior Galvão - MVPMVP segunda-feira, 25 de novembro de 2019 18:03
- Marcado como Resposta IgorFKModerator terça-feira, 10 de dezembro de 2019 18:16
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]
-
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, 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
-
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
-
(...) 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
-
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_fimDeu 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, 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
-
O erro ocorreu ao tentar converter
02832754000133' != '07448732000198para 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. -
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]
-
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_fimDeu 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]
-
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.
- Editado Antonio Luiz Tadeu segunda-feira, 25 de novembro de 2019 15:33 Grafia
- Sugerido como Resposta Junior Galvão - MVPMVP segunda-feira, 25 de novembro de 2019 18:03
- Marcado como Resposta IgorFKModerator terça-feira, 10 de dezembro de 2019 18:16