[Resolvido]Store procedure filtragem por data

Boa noite amigos estou com o pequeno problema aqui referente a uma proc que faz dias que tento fazer e nada o que acontece e que eu uso ela para filtrar os dados de uma tabela no mysql porem gostaria de filtrar tambem entre datas, porem nao consigo o codigo ele mesmo que nao digite nada ele traz todos os dados e colocando o parâmetro ele filtra também.


CREATE DEFINER=`root`@`%` PROCEDURE `sp_conta_relatorio`(
in p_idcandidato int
,in p_status varchar(10)
,in p_tipo varchar(10)
,in p_datainicio date
,in p_datafim date


 
  )
BEGIN

SET @_where = NULL;

SET @sSQL =
'SELECT
        tipo                as ''Tipo''
       ,idconta             as ''Id Conta''
       ,status              as ''Status''
       ,candidato           as ''Candidato''
       ,razao               as ''Razao''
,datalancamento      as ''Data Lancamento''
       ,datavencimento      as ''Data Vencimento''  
       ,valordebito     as ''Valor Debito''
       ,datapago            as ''Data Pago''      
       ,idcandidato         as ''Id Candidato''
,idcliente           as ''Id Cliente''  
       ,cliente             as ''Cliente''  
     
FROM tblcontas';
       
     IF(p_idcandidato IS NOT NULL) THEN
SET @_where = CONCAT(' WHERE idcandidato =',p_idcandidato);
SET @sSQL = CONCAT(@sSQL,@_where);
END IF;

   IF (p_status IS NOT NULL AND @_where IS NOT NULL) THEN
SET @_where = CONCAT(' AND status LIKE ','''%',p_status,'%''');
SET @sSQL = CONCAT(@sSQL,@_where);
END IF;

IF (p_status IS NOT NULL AND @_where IS NULL) THEN
SET @_where = CONCAT(' WHERE status LIKE ','''%',p_status,'%''');
SET @sSQL = CONCAT(@sSQL,@_where);
END IF;
       
 
IF (p_datainicio IS NOT NULL AND @_where  IS NULL) and (p_datafim IS NOT NULL AND @_where  IS NULL)THEN
SET @_where = CONCAT(' and datalancamento between ','''',p_datainicio,''''' and ','''',p_datafim,'''');
       SET @sSQL = CONCAT(@sSQL,@_where);
END IF;


   
   
PREPARE sSQL FROM @sSQL;
EXECUTE sSQL;
DEALLOCATE PREPARE sSQL;

END


tentei de varias formas to empacado nisso se alguém ai puder me ajudar agradeço.

Código:

CREATE PROCEDURE `sp_conta_relatorio`(in datainicio date,datafim date, pesquisa varchar(20)

SELECT
tipo,idconta,status,candidato,razao,datalancamento,datavencimento,valordebito,datapago,idcandidato,idcliente,cliente

FROM tblcontas where datalancamento BETWEEN (datainicio) and (datafim) and  status  like pesquisa or idcandidato  like pesquisa or idcliente  like pesquisa  or cliente  like pesquisa or pesq  like pesquisa;


Para Chamar no Access Exemplo com Lista caso seja campo é só passar o recordset (rs) nos campos.

Para poder pesquisar tudo na tabela tenho sempre um campo com nome pesq onde posso gravar esse codigo */*
Bom Estudo

Código:

Function seleciona_Relatorio()
      Dim cmd As New ADODB.Command
      Dim cn As New ADODB.Connection
      Dim rs As New ADODB.Recordset
10    On Error Resume Next

20            If sErr = -1 Then    'Habilita tratamento de erro
            
30        End If


40        If cn.State = 1 Then    'verificar o status do banco de dados. Se aberto fecha a conexão
50            cn.Close
60        End If
        

70        If rs.State = 1 Then    'verificar o status do recordeset. Se aberto fecha a conexão
80            rs.Close
90        End If
      
100    Set cn = New ADODB.Connection

110    With cn
120    Call MySQL_Server    'Carrega parametros do servidor
130       .ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=" & MyslqServidor & ";Database=" & MyslqDatabase & ";User=" & MyslqUsuario & ";Password=" & MyslqSenha & ";Port=" & MyslqPorta & ";Option=3;"
140   .Open

150      End With

160   With cmd
170           .ActiveConnection = cn
180           .CommandType = adCmdStoredProc
190           .Parameters.Append cmd.CreateParameter("@datainicio", adDate, adParamInput, , Me.Teu Campo)
200           .Parameters.Append cmd.CreateParameter("@datafim", adDate, adParamInput, , Me.Teu Campo)
210           .Parameters.Append cmd.CreateParameter("@pesquisa", adVarChar, adParamInput, 20, Me.Teu Campo)
220           .CommandText = "sp_conta_relatorio"
230       End With
          
240       With rs
250           .CursorLocation = adUseClient
260           .Open cmd, CursorType:=adOpenStatic, _
              Options:=adCmdStoredProc
270           Set .ActiveConnection = Nothing
280       End With

      'Define a propriedade tipo de origem da linha como Lista de Valores.
290   Me.TuaLista.RowSourceType = "Value List"

      'Limpa a origem da linha da listbox.
300   Me.TuaLista.RowSource = ""

      'Define a propriedade número de colunas = 1 coluna.
310   Me.TuaLista.ColumnCount = 11

      'Define a propriedade coluna acoplada = 1 (cujo valores serão utilizados como valor do controle).
320   Me.TuaLista.BoundColumn = 1

      'Define apenas um valor na propriedade largura das colunas, haja vista ter declarado acima apenas 1
      'como número de colunas.
330   Me.TuaLista.ColumnWidths = "0cm;8cm;2cm;3cm;3cm;3cm;0cm;0cm;0cm;0cm;0cm"


      'Move para o inicio do recordset
340   rs.MoveFirst


      'Utiliza a instrução with para agir em cima da ListBox, a cada laço, utiliza o método AddItem para preencher as linhas do controle
350   With rs
360       With Me.Lista
370           Do
            
380          Me.Lista.AddItem rs.Fields("TeuCampo") & ";" & (rs.Fields("TeuCampo") & ";" & rs.Fields("TeuCampo") & ";" & rs.Fields(Format("TeuCampoValor", "standard")) & ";" & rs.Fields(Format("TeuCampoValor", "standard")) & ";" & rs.Fields(Format("TeuCampoValor", "standard")) & ";" & rs.Fields("TeuCampo") & ";" & rs.Fields("TeuCampo") & ";" & rs.Fields("TeuCampo") & ";" & rs.Fields("TeuCampo") & ";" & rs.Fields("TeuCampo"))
              
              
390            rs.MoveNext
                
                  
400           Loop Until rs.EOF
              
              
410       End With
420   End With

      'Atualiza a ListBox
430   Me.TuaLista.Requery


   rs.Close
   cn.Close

   Set rs = Nothing
   Set cn = Nothing
End Function

Boa tarde Avelino João, então seu código já utilizei também porem o problema é o seguinte para relatórios o código que uso faz o seguinte se você pesquisar sem coloca critério nenhum ele me traz todos os registros e se coloca apenas um critério ele trás os dados com somente este critério, no seu código quando é AND ele trás somente os dados correspondentes ao critério e se coloca OR ele trás tudo não faz seleção ao critério informado entende o código meu funciona normal porem falta somente esse problema da data.
Pois eu preciso de 4 critérios por isso não pode usar um campo para pesquisar.
Data Inicial Data Final Tipo D/C status pago ou não idcandidato, ou seja pesquisando a data ele trás ai escolhe o candidato ele traz e assim vai filtrando. endente

Nesse caso voce troca o or para and.

Entao Joao problema e esse se troca ele nao aparece exemplo vc quer pesquisar entre a o primeiro dia do mes e o ultimo dia.

01-09-2019 a 30-09-2019, com aquele script somente isso ja bastaria e traria os dados de todos os candidatos todos pagos e nao pagos e todos os tipos de debitos. ai a medida que for colocando criterio vai filtrando escolhe o candidato ele filtra somente do candidato escolhe os pagos ele filtra somente os pagos.
no caso se coloca and e se se o criterio for vazio ele da erro,
Usando o codigo meu deixando em branco ele traz os dados todos.

chamando a proc call sp_testedata1('2019-09-01','2019-09-30','','','')
Error Code: 1366. Incorrect integer value: '' for column 'p_idcandidato' at row 1

call sp_conta_relatorio(null,null,null,null,null)
3 row(s) returned

resultando se o cliente pesquisar e nao colcar criterio ele retorna como null e tras todos os dados.

É por isso que tenho sempre um campo onde arquivo */* para pesquisar todos os dados. Manda a estrutura da tabela em sql vou testar no meu mysql.

olá Marcelo e Avelino

Se me permitem uma observação, o erro não seria porque a montagem do @_where esta começando com And?


IF (p_status IS NOT NULL AND @_where IS NOT NULL) THEN
SET @_where = CONCAT(' and status LIKE ','''%',p_status,'%''');
SET @sSQL = CONCAT(@sSQL,@_where);
END IF;


IF (p_datainicio IS NOT NULL AND @_where IS NULL) and (p_datafim IS NOT NULL AND @_where IS NULL)THEN
SET @_where = CONCAT(' and datalancamento between ','''',p_datainicio,''''' and ','''',p_datafim,'''');
SET @sSQL = CONCAT(@sSQL,@_where);
END IF;

Boa noite marcos entao o restante do codigo funciona o problema é a parte com data que eu nao estou conseguindo fazer funcionar.

Boa noite avelino e esta.

CREATE TABLE `tblcontas` (
`idconta` INT(11) NOT NULL AUTO_INCREMENT,
`status` VARCHAR(10) NULL DEFAULT '0',
`datalancamento` DATE NULL DEFAULT NULL,
`datavencimento` DATE NULL DEFAULT NULL,
`tipo` VARCHAR(20) NULL DEFAULT NULL,
`razao` VARCHAR(100) NULL DEFAULT NULL,
`valordebito` FLOAT(10,2) NULL DEFAULT NULL,
`valorpago` FLOAT(10,2) NULL DEFAULT NULL,
`formapg` VARCHAR(10) NULL DEFAULT NULL,
`datapago` DATE NULL DEFAULT NULL,
`idcandidato` INT(11) NULL DEFAULT NULL,
`candidato` VARCHAR(100) NULL DEFAULT NULL,
`idcliente` INT(11) NULL DEFAULT NULL,
`cliente` VARCHAR(100) NULL DEFAULT NULL,
`obs` VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (`idconta`),
INDEX `idcandidato` (`idcandidato`),
INDEX `idcliente` (`idcliente`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=42
;

preciso dos dados sql para inserir na tabela

Bom dia Avelino ta ai script completo

-- --------------------------------------------------------
-- Servidor: 127.0.0.1
-- Versão do servidor: 8.0.16 - MySQL Community Server - GPL
-- OS do Servidor: Win64
-- HeidiSQL Versão: 9.3.0.4984
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Copiando estrutura do banco de dados para agenciadb
CREATE DATABASE IF NOT EXISTS `agenciadb` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `agenciadb`;


-- Copiando estrutura para tabela agenciadb.tblcontas
CREATE TABLE IF NOT EXISTS `tblcontas` (
`idconta` int(11) NOT NULL AUTO_INCREMENT,
`status` varchar(10) DEFAULT '0',
`datalancamento` date DEFAULT NULL,
`datavencimento` date DEFAULT NULL,
`tipo` varchar(20) DEFAULT NULL,
`razao` varchar(100) DEFAULT NULL,
`valordebito` float(10,2) DEFAULT NULL,
`valorpago` float(10,2) DEFAULT NULL,
`formapg` varchar(10) DEFAULT NULL,
`datapago` date DEFAULT NULL,
`idcandidato` int(11) DEFAULT NULL,
`candidato` varchar(100) DEFAULT NULL,
`idcliente` int(11) DEFAULT NULL,
`cliente` varchar(100) DEFAULT NULL,
`obs` varchar(100) DEFAULT NULL,
PRIMARY KEY (`idconta`),
KEY `idcandidato` (`idcandidato`),
KEY `idcliente` (`idcliente`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;

-- Copiando dados para a tabela agenciadb.tblcontas: ~2 rows (aproximadamente)
/*!40000 ALTER TABLE `tblcontas` DISABLE KEYS */;
INSERT INTO `tblcontas` (`idconta`, `status`, `datalancamento`, `datavencimento`, `tipo`, `razao`, `valordebito`, `valorpago`, `formapg`, `datapago`, `idcandidato`, `candidato`, `idcliente`, `cliente`, `obs`) VALUES
(40, 'PAGO', '2019-09-05', NULL, 'C', 'MARCELO ANTONIO FREITAS DA SILVA', 49.99, 49.99, NULL, '2019-09-05', 1, 'MARCELO ANTONIO FREITAS DA SILVA', NULL, NULL, 'Conta Referente a Pagamento de Taxa de Inscrição'),
(41, 'ABERTO', '2019-09-07', '2019-09-07', 'D', NULL, 150.00, NULL, NULL, NULL, 1, 'MARCELO ANTONIO FREITAS DA SILVA', NULL, NULL, NULL);
/*!40000 ALTER TABLE `tblcontas` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Opa, entendi Marcelo!
Está montando a cláusula where com os ifs, só que esse cara tem uma sequência a ser obedecida, coisa que usuário não segue.

Eu particularmente estou evitando usar tratar regras desse tipo com SGBD, tratar no lado da aplicação a manutenção fica mais fácil.

troque para no IF das datas para ver.
@_where NOT IS NULL

Se o usuário colocar só datainicial e datafinal. Como fica a variável @where? Precisa de pelo menos de algum outro filtro preenchido para dar início ao where.

Boa tarde marcos então ai que ta se o usuário não colocar nenhum filtro ele trás todos os dados ele entende que o parâmetro e null e retorna todos os dados.
por isso uso este ai pq se fizer where and and ou or ele necessita de preenchimento e se for or ele nao filtra os dados, na verdade quem me ajudou muito nessa questão e construiu o filtro foi o amigo Alexandre fim que e fera nisso mais tentei entra em contato com ele pra ele me ajudar e acho que ele ta meio ocupado não consigo retorno dele. Em relação a programar dentro da aplicação a ideia e deixa a aplicação mais leve e fazer com que o processamento rode no servidor e não na aplicação ficando assim mais leve e o ocupando a memoria.

entende a questao entre data eu que to tentando de varias formas mais todas sem sucesso.

Marcelo, não é simplesmente rodar do lado do servidor que tudo está resolvido. Depende muito do tipo de recordset a ser retornado, que inclusive se for rodado no lado do servidor pode deixar a aplicação mais lenta.

Veja um trecho do artigo deste link (macoratti.net/ado_fast.htm) - que sugiro a leitura completa.

5-) Seja muito criterioso ao selecionar o Tipo de Cursor , a localização do Cursor e o tipo de bloqueio.

Se você não precisa fazer atualizações nem se mover para frente e para trás em seu Recordset , não tenha dúvidas utilize o padrão ADO : adUseServer , adOPenForwardOnly e adLockReadOnly. Isto lhe dará um melhor desempenho.

Se você precisar se mover por todo o seu Recordset, o cenário muda , prefira usar o Cursor do lado do cliente - adUseClient , e neste caso utilize adLockReadOnly como tipo de bloqueio (a não ser que você realmente precisa de outro tipo de bloqueio).

Já se você precisar de um Recordset atualizável o Cursor do lado do cliente - adUseClient - irá onerar mais o seu sistema. Utilize AdUseServer.

O Ivan boa noite sim como você havia me recomendado eu li sim o artigo, e minha aplicação tem um desempenho bom em relação aos recorset a demora para trazer os dados e considerável levando em conta a velocidade da net que varia muito e também o servidor mysql que e compartilhado mais esta bom e carrega de forma rápida problema maior e essa proc de filtragem que ta dando show.

Logo postarei a forma exata do filtro consegui falar com o alexandre que vai me da uma ajudinha

Alguma novidade aqui?

Olá a todos,

Marcelo Antonio, como ficou a situação?
Os membros merecem e agradecem o retorno.

Ficamos aguardar.

Abraço a todos

Alguma novidade aqui? Creio que sim, Marcelo...

Aguardamos retorno e, se for o caso, encerramento do tópico.

Ola Pessoal Resolvi o Probelma com a seguinte procedure.

CREATE DEFINER=`lotuse55_admin`@`%` PROCEDURE `spRelatorioEncaminhados`(
IN p_where TEXT
)
BEGIN

SET @sSQL =
'SELECT
idencaminhamento,
idcliente,
cliente,
idcandidato,
candidato,
dataencaminhamento,
responsavelrecrutamento,
idvaga,
idprofissao,
profissao,
dataentrevista,
horaentrevista,
dataretorno,
historico,
status
FROM tblencaminhamentohist';

IF(p_where IS NOT NULL) THEN
SET @sSQL = CONCAT(@sSQL,p_where);
END IF;

PREPARE sSQL FROM @sSQL;
EXECUTE sSQL;
DEALLOCATE PREPARE sSQL;

END

e no form no access


'Configura o Listbox da pesquisa
Private Sub MontaLista()

Dim sqlDAO As String
'On Error GoTo trata_erro

Set DB = CurrentDb
DB.Execute ("DELETE FROM Grid_001_Frm_Encaminhamento")
'Set rsdao = db.OpenRecordset("SELECT * FROM Grid_001_Frm_Encaminhamento")
Me.Grid_001_Frm_Encaminhamento.Requery

strWhere = " WHERE "
strColunas = Empty

If Not IsNull(CBXCLIENTE) Or Trim(CBXCLIENTE) <> Empty Then
strWhere = strWhere & " idcliente = ''" & CBXCLIENTE.Column(0) & "''"
strWhere = strWhere & " AND "
End If

If Not IsNull(CBXSTATUS) Or Trim(CBXSTATUS) <> Empty Then
strWhere = strWhere & " status = ''" & CBXSTATUS & "''"
strWhere = strWhere & " AND "
End If


If Not IsNull(datainicio) And Not IsNull(datafim) Then
strWhere = strWhere & " dataencaminhamento BETWEEN ''" & Format(datainicio, "yyyy-mm-dd") & "''"
strWhere = strWhere & " AND ''" & Format(datafim, "yyyy-mm-dd") & "''"
strWhere = strWhere & " AND "
End If

strWhere = Mid(Trim(strWhere), 1, Len(Trim(strWhere)) - 3)

Call Conecta

cSQL = "CALL spRelatorioEncaminhados (' " & strWhere & "') "
rsdados.Open cSQL, cn, adOpenKeyset, adLockReadOnly

'Informa a variavel booleana conforme a contagem de registros
blnAchou = IIf((rsdados.RecordCount) > 0, True, False)

'If Not rsdados Then
Do While Not rsdados.EOF
sqlDAO = Empty
sqlDAO = "INSERT INTO grid_001_frm_encaminhamento "
sqlDAO = sqlDAO & " ("
sqlDAO = sqlDAO & " idencaminhamento"
sqlDAO = sqlDAO & " ,idcliente"
sqlDAO = sqlDAO & " ,cliente"
sqlDAO = sqlDAO & " ,idcandidato"
sqlDAO = sqlDAO & " ,candidato"
sqlDAO = sqlDAO & " ,dataencaminhamento"
sqlDAO = sqlDAO & " ,responsavelrecrutamento"
sqlDAO = sqlDAO & " ,idvaga"
sqlDAO = sqlDAO & " ,idprofissao"
sqlDAO = sqlDAO & " ,profissao"
sqlDAO = sqlDAO & " ,dataentrevista"
sqlDAO = sqlDAO & " ,horaentrevista"
sqlDAO = sqlDAO & " ,dataretorno"
sqlDAO = sqlDAO & " ,historico"
sqlDAO = sqlDAO & " ,status"
sqlDAO = sqlDAO & " )"
sqlDAO = sqlDAO & " VALUES"
sqlDAO = sqlDAO & " ("
sqlDAO = sqlDAO & " " & rsdados("idencaminhamento").Value & ""
sqlDAO = sqlDAO & " ," & rsdados("idcliente").Value & ""
sqlDAO = sqlDAO & " ,'" & rsdados("cliente").Value & "'"
sqlDAO = sqlDAO & " , " & rsdados("idcandidato").Value & ""
sqlDAO = sqlDAO & " ,'" & rsdados("candidato").Value & "'"
sqlDAO = sqlDAO & " ,'" & rsdados("dataencaminhamento").Value & "'"
sqlDAO = sqlDAO & " ,'" & rsdados("responsavelrecrutamento").Value & "'"
sqlDAO = sqlDAO & " , " & rsdados("idvaga").Value & ""
sqlDAO = sqlDAO & " , " & rsdados("idprofissao").Value & ""
sqlDAO = sqlDAO & " ,'" & rsdados("profissao").Value & "'"
sqlDAO = sqlDAO & " ,'" & rsdados("dataentrevista").Value & "'"
sqlDAO = sqlDAO & " ,'" & rsdados("horaentrevista").Value & "'"
sqlDAO = sqlDAO & " ,'" & rsdados("dataretorno").Value & "'"
sqlDAO = sqlDAO & " ,'" & rsdados("historico").Value & "'"
sqlDAO = sqlDAO & " ,'" & rsdados("status").Value & "'"
sqlDAO = sqlDAO & " )"
DB.Execute sqlDAO

rsdados.MoveNext
Loop
Me.Grid_001_Frm_Encaminhamento.Requery
'End If

Call FN_FechaRst(rsdados)


Exit Sub

'trata_erro:

'Call FN_FechaRst(rsdados)

'Exit Sub


End Sub


ficou assim no botão pesquisar

sendo assim resolveu o problemas graças ao amigo Alexandre fim.