Stored Procedure Doesn’t Deliver Results You Should

Question

-I am very new in SQL, Spring & Java, I am trying to set the results of a list that returns in a Query I created

-this query returns a list with the referenced object

-the parameters are all right, including the table name (which is variable)

PROBLEM: The query running in the controller brings no results, while if I run manually in SQL Management, the results are obtained.

QUERY CODE:

ALTER PROCEDURE [dbo].[usp_listarRegistrosMov]
    --PARÂMETROS
    @NomeTabela VARCHAR(20),
    @DataInicial VARCHAR(20),
    @DataFinal VARCHAR(20),
    @Cracha FLOAT


AS
BEGIN


Declare @Comando Varchar(1000)

Set @Comando = 'SELECT * FROM ' + @NomeTabela + ' WHERE mov_data BETWEEN ' + @DataInicial + ' AND ' + @DataFinal + ' AND mov_cracha = ' + CAST(@Cracha AS VARCHAR(20))

Exec(@Comando)



END


GO

CONTROLLER CODE:

    public void consultar() {
        LocalDate dataInicio = dataInicial.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        LocalDate dataFim = dataFinal.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        String mesInicio = String.valueOf(dataInicio.getMonthValue());
        String mesFim = String.valueOf(dataFim.getMonthValue());
        String anoInicio = (String.valueOf(dataInicio.getYear())).substring(2,4);
        String anoFim = (String.valueOf(dataFim.getYear())).substring(2,4);
        String empresaString = String.valueOf(Math.round(funcionario.getEmpresa().getCodigo()));
        long mesesDiferencaGlob = ChronoUnit.MONTHS.between(dataInicio, dataFim) + 1;

        if (dataInicio.isAfter(dataFim)) {
            Messages.addGlobalError("Informe uma data inicial posterior à data final");
            return;
        }

        if (dataInicio.getMonthValue() != dataFim.getMonthValue()) {
            if (dataInicio.getYear() == dataFim.getYear()) {
                do {
                    System.out.println(mesesDiferencaGlob);
                    String tabela = ("M00"+(String.valueOf(Math.round(funcionario.getEmpresa().getCodigo())))+anoInicio+"0"+mesInicio);
                    System.out.println(tabela);
                    DateTimeFormatter formatadorInicio = DateTimeFormatter.ofPattern("dd/MM/yyyy");
                    String dataInicioString = dataInicio.format(formatadorInicio);
                    String dataFimString = dataFim.format(formatadorInicio);
                    System.out.println(dataInicioString + dataFimString);
                    setRegistrosTemp(eventoEspelhoPontoRepository.findAllRegistrosByFuncionarioTableUnica(tabela, dataInicioString, dataFimString, funcionario.getCracha()));
                    for (EventoEspelhoPonto item : registrosTemp) {
                        registros.add(item);                        
                    }
                    int mesInicioInt = Integer.parseInt(mesInicio) + 1;
                    mesInicio = Integer.toString(mesInicioInt);
                    mesesDiferencaGlob--;
                } while (mesesDiferencaGlob != 0);      
            }   
        }
}

REPOSITORY CODE:

@Query(value = "EXECUTE usp_listarRegistrosMov :tabela, :dataInicial, :dataFinal, :cracha", nativeQuery = true)
List<EventoEspelhoPonto> findAllRegistrosByFuncionarioTableUnica(@Param("tabela") String tabela,
                                                                 @Param("dataInicial") String dataInicial,
                                                                 @Param("dataFinal") String dataFinal,
                                                                 @Param("cracha") Double cracha);

Answer 1

I fixed it by switching my STORED PROCEDURE to the following:

Set @Comando = 'SELECT * FROM ' + @NomeTabela + 
               ' WHERE mov_data BETWEEN ''' + @DataInicial + ''' AND ''' + @DataFinal + ''' AND mov_cracha = ' + CAST(@Cracha AS VARCHAR(20))

missing single quotes between dates!

I hope it helps someone. Hugs!

Answer 2

hi @citaox

The select you are running directly is different from what you run by the code. Take a look at the final date that in the database is 05/04/2019

Abs