CODEWITHSUNDEEP
javamysqlstored-procedures
user1047873
user1047873

Reputation: 268

How to return a result-set from second select statement in a procedure?

How can I return a result-set from a second select statement

I have the sample: 

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getGender20`(in id INT)
BEGIN
 DECLARE str VARCHAR(50);
  select @str :=gender from memeber_info where id=id;
if (str = 'Male') then
select '12345' ;
end if;
select '123' ;
END

My procedure is retuning male while I need it to return 123. How this can be achieved?

Upvotes: 0

Views: 106

Answers (2)

Patrick
Patrick

Reputation: 17973

In java you can get the next result set from the Statement using the getMoreResults function. The method returns a boolean, which is true if more result sets are available.

Upvotes: 0

juergen d
juergen d

Reputation: 204746

You can do that without a variable by using case

select case when gender = 'Male'
            then 12345
            else 123
       end as some_name
from memeber_info
where id=id;

Upvotes: 1

Related Questions