Stored procedure in SQL Server does not return a result set when called by java

Question

I have the following stored procedure and my goal is to return a result set from it, using the query "select * from #justTmp"

create procedure spAddPerson
    @ID int,
    @Name nvarchar(50)
as
begin
    create table #justTmp
    (
        num int primary key,
        justName nvarchar(50)
    )

    if @ID in (select C.ContributorID
               from Contributors C)
    begin
        insert into #justTmp
        values (@ID, @Name)
    end

    select *
    from #justTmp
end

And this is my java code

CallableStatement cstmt = null; 
ResultSet rs = null;

cstmt=conn.prepareCall("{ call spAddPerson(?,?) }");
cstmt.setInt(1, 1);
cstmt.setString(2, "MyName");

cstmt.execute();
rs=cstmt.getResultSet();
int first=rs.getInt(1);
String second=rs.getString(2);

For some reason, whenever I run it, and the above stored procedure is called, I get an exception that says

The statement did not return a result set

when the

rs = cstmt.getResultSet()

code is executed.

Any idea how to fix this so a result set would return and not null?

Thanks in advance

@Ian-Fogelman How? Like this? create proc spAddPerson SET NOCOUNT ON? — Ori Lev, Oct 19 '19 at 02:11
If you run the stored procedure manually (like in SSMS) with the same `@ID` the Java program uses, do you get a result set? Also, on the line after `begin` is where to put `SET NOCOUNT ON`. — user212514, Oct 19 '19 at 03:24
@user212514 Yes, I do get result set running it manually in SSMS — Ori Lev, Oct 19 '19 at 06:52

score 0 · Answer 1 · answered Oct 19 '19 at 02:14

0

Try this :

create proc spAddPerson
    @ID int,
    @Name nvarchar(50)
    as
    begin
SET NOCOUNT ON
    ...

answered Oct 19 '19 at 02:14

Ian-Fogelman

1,595
1
9
15

I get "The result set has no current row" while running the java, but if I try to execute the procedure directly in SQL Server it does return result set – Ori Lev Oct 19 '19 at 02:30

score 0 · Answer 2 · answered Oct 19 '19 at 02:37

0

There are multiple ways but try this once:

create proc spAddPerson @ID int, @Name nvarchar(50) as begin SET NOCOUNT ON;

    create table #justTmp
    (
    num int primary key,
    justName nvarchar(50)
    )


    if @ID  in
    (
      select C.ContributorID
      from Contributors C
    )
    begin
    insert into #justTmp
    values(@ID,@Name)
    end

    select *
    from #justTmp

 RETURN
end

answered Oct 19 '19 at 02:37

Abhishek Singh

1
2

I get the error message "The result set has no current row" running the java. – Ori Lev Oct 19 '19 at 02:46

score 0 · Answer 3 · answered Oct 19 '19 at 02:54

0

On your java code change

call spAddPerson

To

exec spAddPerson

answered Oct 19 '19 at 02:54

Goose

546
3
7

Stored procedure in SQL Server does not return a result set when called by java

3 Answers3