jdbc sql 错误:语句没有返回结果集 [英] jdbc sql error: statement did not return a result set

问题描述

我有两个存储过程如下:

I have two stored procedures as follows:

create stored procedure p1
as
    select * from table1 where datediff(day, table1.[date], getdate())

create stored procedure p2
as
   declare @t1 table(
     ref varchar(20)
   )
      insert into @t1 select * from table1 where ref = 'some ref'
      declare @t2 table(
      fname varchar(20),
      lname varchar(20),
      email varchar(1000)
  )
  declare @len int = (select count(ref) from @t1)
  while @len > 0
  begin
  declare @value varchar(20)  = (select top 1 ref from @t1)
  insert into @t2 select * from table2 where ref = @ref
  delete from @t1
  where ref = @value
  set @len = (select count(ref) from @t1)
  end
  select * from @t2

Java 代码

 ....
 String query = "Execute [p2]";

 try(CallableStatement cstmt = conn.prepareCall(query);
     ResultSet rs = cstmt.executeQuery()){
        ... some code
    }

表变量@t1 保存从表 'table1' 中选择的结果

The table variable @t1 hold select result from a table 'table1'

变量@len 保存@t1 中的行数

The variable @len hold the number of rows in @t1

使用<代码>@len >0 作为while循环中的条件，我想从另一个表'table2'中选择记录表变量@t2保存来自'table2'的选择记录

Using @len > 0 as condition in while loop, I want to select records from another table 'table2' the table variable @t2 hold the select records from 'table2'

delete 语句从@t1 中删除值@len 设置为@t1 中的新行数最后一条语句返回@t2中存储的所有记录

The delete statement removes value from @t1 @len set to new number of rows in @t1 the last statement return all the records store in @t2

第一个过程运行良好，但第二个过程仅适用于 SQL Server.

The first procedure works fine, but the second procedure works only in SQL Server.

我在我的 Java 应用程序中收到一条错误消息

I get this an error message in my java application

语句没有返回结果集

我希望它返回一个结果集，其中包含我在查询结束.

I want this to return a result set with the select statement I have at the end of the query.

请问有没有办法解决这个问题?

Please is there a way around this?

推荐答案

你的 [p2] 存储过程需要在开头包含 SET NOCOUNT ON 以抑制n 受影响的行数"计数，因此 JDBC 不会对它应该放入 ResultSet 的内容感到困惑:

Your [p2] stored procedure needs to include SET NOCOUNT ON right at the beginning to suppress the "n rows affected" counts so JDBC doesn't get confused as to what it should put into the ResultSet:

CREATE PROCEDURE p2
AS

SET NOCOUNT ON;

declare @t1 table(
    ref varchar(20)
)

-- ... and so on

有关 SET NOCOUNT 的更多信息，请参阅

For more information on SET NOCOUNT see

SET NOCOUNT (Transact-SQL)

有关从存储过程调用返回的确切内容的详细信息，请参阅

For more information on precisely what gets returned from a stored procedure call, see

如何从使用 JDBC 的存储过程获取一切

这篇关于jdbc sql 错误:语句没有返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！