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

声明未返回结果集

statement did not return a resultset

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

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

设置NOCOUNT(Transact-SQL)

有关从存储过程调用中返回的确切信息的更多信息，请参见

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

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

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