Stored procedure with 3 select statements only returns a single table

Question

When I call my stored procedure with 3 select queries in mysql, then it returns 3 result sets, but when I call it in spring it only returns a single result set.

Below is the code calling the stored procedure in Spring:

@ResponseBody
    @RequestMapping(value="/", method=RequestMethod.GET, produces="application/json", headers="Accept=application/json")
    public String listProducts() throws ParseException {
        int id=1;
        int days=20;
        Query query = session.createNativeQuery("{call getWalkInInfo(?,?)}", Product.class);
        query.setParameter(1,id);
        query.setParameter(2,days);
        Gson gson=new Gson();
        String jsonArray=gson.toJson(query.getResultList());
        System.out.println(jsonArray);
        return jsonArray;
    }

@Adamszsz Not true. It will display all of the select statement. — Hermanto, Jul 29 '21 at 06:33
Does this answer your question? [How do you get multiple resultset from a single CallableStatement?](https://stackoverflow.com/questions/20254115/how-do-you-get-multiple-resultset-from-a-single-callablestatement) — Hermanto, Jul 29 '21 at 06:33

Adamszsz · Answer 1 · 2021-07-29T06:43:32.163

-1

You need to create 3 OUTPUTs in your stored procedure

for example MSSQL server :

ALTER PROCEDURE [dbo].[Procedure_name]
(
@Id_user int,
@select_one int output,
@select_two decimal(10,2) output,
@select_three decimal(10,2) output 
)
AS

BEGIN

set @select_one = (select * from table1);
set @select_two = (select * from table2);
set @select_three = (select * from table3);

END

edited Jul 29 '21 at 06:43

answered Jul 29 '21 at 06:37

Adamszsz

561
3
13

Stored procedure with 3 select statements only returns a single table

1 Answers1