java - 选择计算动态查询的存储过程无法正常工作

Question

-我正在尝试执行一个应该返回计数结果的存储过程，但它给了我以下错误(葡萄牙语):

2019-04-01 10:13:49.215  WARN 7068 --- [io-8080-exec-10] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 214, SQLState: S0002
2019-04-01 10:13:49.216 ERROR 7068 --- [io-8080-exec-10] o.h.engine.jdbc.spi.SqlExceptionHelper   : O procedimento espera o parâmetro '@statement' do tipo 'ntext/nchar/nvarchar'.

-我不太明白，因为我设置的唯一参数是表名称，我正在将其传递给存储库执行代码。

-这是我的存储过程:

ALTER PROCEDURE [dbo].[usp_verificarTabelaCadMov] (
    @NomeTabela VARCHAR(20)

)   
AS
BEGIN
    SET NOCOUNT ON;

Declare @Comando Varchar(1000)
Declare @Resultado INT;

Set @Comando = 'SELECT @Resultado = COUNT(*) FROM sysobjects WHERE nome = ' + QUOTENAME(@NomeTabela)

exec sp_executesql @Comando , N'@Resultado INT OUTPUT', @Resultado = @Resultado OUTPUT

SELECT @Resultado

END;

GO

-这是我在存储库中的执行:

@Query(value = "EXECUTE usp_verificarTabelaCadMov :tabela", nativeQuery = true)
public Integer verificarTabela(@Param("tabela") String tabela);

-在我的 Controller 中:

                    String tabela = ("M00"+(String.valueOf(Math.round(funcionario.getEmpresa().getCodigo())))+anoInicio+mesAtual);
                    if (eventoEspelhoPontoRepository.verificarTabela(tabela) > 1) {
                        registros.addAll(eventoEspelhoPontoRepository.findAllRegistrosByFuncionarioTableUnica(
                                tabela, dataInicioString, dataFimString, funcionario.getCracha()));
                    }

编辑1 - 我在这里想做的是选择一行表，但有时某些表不存在，所以我试图验证该表是否存在以及是否存在，它应该执行 Select * from that table，否则，应该忽略。这是我发现的唯一方法，如果有人有更好的方法请帮助我。

顺便说一句，这是我上面提到的选择，我尝试使用子句 IF EXISTS 来验证表是否存在，但它不起作用，我在主程序上不断收到错误“无法提取结果集”。

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

AS  

IF EXISTS(SELECT name FROM sysobjects WHERE name = @NomeTabela AND xtype = 'U')

BEGIN

Declare @Comando Varchar(1000)

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

Exec(@Comando)

END



GO

编辑2:我也尝试在同一个sp中使用OBJECT_ID:

IF OBJECT_ID(@NomeTabela) IS NOT NULL

但总是出现错误:

2019-04-01 11:18:03.147  WARN 7068 --- [nio-8080-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: null
2019-04-01 11:18:03.148 ERROR 7068 --- [nio-8080-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : The statement did not return a result set.

Answer 1

我会做这样的事情:

ALTER PROCEDURE dbo.usp_verificarTabelaCadMov
(
  @NomeTabela VARCHAR(20)
)
AS
  BEGIN
    SET NOCOUNT ON;

    DECLARE @Comando NVARCHAR(1000);
    DECLARE @Resultado INT;

    SET @Resultado = -1

        -- Or set to NULL depending on how you would like to handle table existence in the application code;
        --SET @Resultado = NULL

    IF OBJECT_ID(QUOTENAME(@NomeTabela), 'U') IS NOT NULL
      BEGIN
        SET @Comando = N'SELECT @Resultado = COUNT(*) FROM ' + QUOTENAME(@NomeTabela);

        EXEC sys.sp_executesql
          @Comando
          , N'@Resultado INT OUTPUT'
          , @Resultado OUTPUT;
      END;

    SELECT @Resultado AS resultado;
  END;

因此，根据您选择如何处理表存在，调用

EXEC dbo.usp_verificarTabelaCadMov @NomeTabela = 'thistabledoesNOTexist';

将返回-1或NULL(参见存储过程注释)和

EXEC dbo.usp_verificarTabelaCadMov @NomeTabela = 'thistableDOESexist';

将返回一个值 > -1。