The statement did not return a result set during calling stored procedure

Question

String dateString = null;
SimpleDateFormat sd = new SimpleDateFormat("yyyy/MM/dd");
dateString = sd.format(pumpTime);

PreparedStatement st = con.prepareStatement("{call dbo.HrMin(?,?)}");   st.setString(1,dateString);      
st.setInt(2,7);   
ResultSet rs = st.executeQuery();    while(rs.next())  {}  

procedure is :
USE [NC26]
GO

/****** Object:  StoredProcedure [dbo].[HrMin]    Script Date: 03/29/2016 15:40:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[HrMin]
    -- Add the parameters for the stored procedure here
    @date varchar(10),@tagindex smallint
AS
BEGIN

declare @datenew datetime;
-- To datetime datatype
set @datenew=DATEADD(DD,-1,(SELECT CONVERT(datetime, @date)));
set @datenew=CONVERT(varchar(10), @datenew, 111);
create table #finalresults
(
    hr int,
    val float

)

-- insert the survey table structures for use

insert into #finalresults (hr, val)
Select distinct hr,(Select top 1 val from hrtableView where tagindex=@tagindex 
and dt=@date and hr=t1.hr ) from hrtableView t1 where tagindex =@tagindex
and dt=@date  group by hr;


insert into #finalresults (hr, val)
Select distinct hr,(Select top 1 val from hrtableView1 where tagindex=@tagindex 
and dt=@date and hr=t1.hr ) from hrtableView1 t1 where tagindex =@tagindex
and dt=@date  group by hr;

SELECT hr,val FROM #finalresults
drop table #finalresults

END

GO

error: The statement did not return a result set.

I have used callable statement but got same error. I have used so many tricks from Google, but got same error again and again.

PreparedStatement st = con.prepareStatement("{call dbo.HrMin(?,?)}"); st.setString(1,dateString); st.setInt(2,7); boolean rs = st.execute(); System.out.println("rss" + rs); then it prints rss false — Ayush Bhardwaj, Mar 29 '16 at 11:09

score 0 · Answer 1 · answered Mar 30 '16 at 08:20

0

Put SET NOCOUNT ON in procedure will resolve the error

answered Mar 30 '16 at 08:20

Ayush Bhardwaj

11
1

I think you should combine both of your answers into one answer and delete the other answer. – Mark Rotteveel Mar 30 '16 at 08:32

score 0 · Answer 2 · answered Mar 30 '16 at 08:23

USE [NC26]
GO

/****** Object:  StoredProcedure [dbo].[test]    Script Date: 03/30/2016 10:26:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[test] 
    -- Add the parameters for the stored procedure here
    @date varchar(10),@tagindex smallint
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @datenew datetime;

    create table #finalresults
(
    hr int,
    val float

)

insert into #finalresults (hr, val)
Select distinct hr,(Select top 1 val from hrtableView where tagindex=@tagindex 
and dt=@date and hr=t1.hr ) from hrtableView t1 where tagindex =@tagindex
and dt=@date  group by hr;


insert into #finalresults (hr, val)
Select distinct hr,(Select top 1 val from hrtableView1 where tagindex=@tagindex 
and dt=@date and hr=t1.hr ) from hrtableView1 t1 where tagindex =@tagindex
and dt=@date  group by hr;




SELECT hr,val FROM #finalresults
drop table #finalresults
END

GO

The statement did not return a result set during calling stored procedure

2 Answers2