stored procedure returns just one row of multiple rows

Question

I will start by giving you my table content

This is my stored procedure:

ALTER PROCEDURE dbo.getFoodsForOrder
    (
    @orderID INT,
    @ID INT OUTPUT,
    @food_restaurantID INT OUTPUT,
    @count INT OUTPUT,
    @description VARCHAR(200) OUTPUT
    )
AS
SET NOCOUNT ON
BEGIN
    SELECT [ID],
    [food_restaurantID],
    [count],
    [description]
    FROM Order_Food
    WHERE orderID = @orderID
END

My Problem

when I call the stored procedure from JDBC like this

Connection con = Database.getConnection();
        CallableStatement callableStatement = null;
        try {
            callableStatement = con
                    .prepareCall("{call getFoodsForOrder(?,?,?,?,?)}");
            callableStatement.setInt(1, getID());
            callableStatement.registerOutParameter(2, java.sql.Types.INTEGER);
            callableStatement.registerOutParameter(3, java.sql.Types.INTEGER);
            callableStatement.registerOutParameter(4, java.sql.Types.INTEGER);
            callableStatement.registerOutParameter(5, java.sql.Types.VARCHAR);
            System.out.println("ID   = " + getID());
            boolean haveResult = callableStatement.execute();
            while (haveResult) {
                System.out.println("here I am");
                haveResult = callableStatement.getMoreResults();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (callableStatement != null)
                    callableStatement.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

it just print here I am once, just once, even If (like the picture said) I have more than 15 rows are ture.

you problem will ask me if I am sure about the getID() method, Yes I am sure and even when I replace it with explicit 10 , the result doesn't change.

Something might help

when I call that stored procedure from my Visual studio , I got 17 results like this:

Any help will be appreciated

Answer 1

The callableStatement.execute() indicates if a result set is available.

getMoreResults() only indicates if there are is another ResultSet available, not if the "last" result set has more rows (which is - I think - pretty clear from the JavaDocs). getMoreResults() would return true if you had more than one select statement in your procedure.

If execute() indicates a ResultSet is available you need to obtain it using callableStatement.getResultSet() and then iterate over the rows returned by that.

Something like:

boolean haveResult = callableStatement.execute();
if (haveResult) {
   ResultSet rs = callableStatement.getResultSet();
   while (rs.next()) {
      System.out.println(rs.getInt(1));
   }
} else {
  System.out.println("Nothing returned");
}

But as you know it returns a result set you can also call getResultSet() right away without even bothering about the return value of the execute() call.

Answer 2

You need to remove the OUTPUT variables from your stored procedure and no longer register them in your code. You then need to use executeQuery() to get the resultset and iterate over the resultset to get rows.

Assuming a table structure of:

CREATE TABLE [dbo].[Order_Food](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [OrderID] [int] NULL,
    [food_restaurantID] [int] NULL,
    [count] [int] NULL,
    [description] [nvarchar](255) NULL,
 CONSTRAINT [PK_Order_Food] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Table content:

INSERT INTO [dbo].[Order_Food] 
     ([OrderID], [food_restaurantID], [count], [description])
     VALUES (513, 2, 3, 'Description xyz'), (513, 2, 3, 'Description xyz'), 
        (132, 1, 2, 'Description abc');

A stored procedure as:

CREATE PROCEDURE dbo.getFoodsForOrder
    (
    @orderID INT
    )
AS
SET NOCOUNT ON
BEGIN
    SELECT [ID],
    [food_restaurantID],
    [count],
    [description]
    FROM Order_Food
    WHERE orderID = @orderID
END

And query code as:

public static void main(String[] args) throws SQLException {
    try (
        Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=Scratchspace;integratedSecurity=true");
        CallableStatement callableStatement = 
            con.prepareCall("{call getFoodsForOrder(?)}");
    ){
        callableStatement.setInt(1, 513);
        System.out.println("ID   = " + 513);
        ResultSet rs = callableStatement.executeQuery();
        while (rs.next()) {
            System.out.printf("%d %d %d %s%n",
                    rs.getInt("ID"), rs.getInt("food_restaurantID"),
                    rs.getInt("count"), rs.getString("description"));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

On my computer this produces the output:

ID   = 513
1 2 3 Description xyz
2 2 3 Description xyz