问题描述

我有两个存储过程如下:

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 的内容感到困惑:

CREATE PROCEDURE p2
AS

SET NOCOUNT ON;

declare @t1 table(
    ref varchar(20)
)

-- ... and so on