Having multiple SQL statements within a STORED PROCEDURE returning values
Hi,
I am trying to have 1 stored procedure that contains 2 different sql statements, which must return values. However, i noticed that these 2 sql statements are run one after the other, therefore, when i am calling the stored procedure, only the first sql runs, which fails the return value of the second sql statement. Here is my stored procedure: I would expect the stored procedure to return all values after it executes.. Is my only other option to create 3 different stored procedures, 2 with the below sql statements and 1 calling the 2 ? Or whats my best option. Thanks Naren
CREATE DEFINER=`root`@`localhost` PROCEDURE `view_sr_projects_detail`(
IN userName varchar(20),
INOUT projectCode int,
OUT projectName text,
OUT projectType text,
OUT projectDescription text,
OUT proj_min_num double,
OUT proj_max_num double)
BEGIN
SELECT
project_code as projectCode,
project_name as projectName,
project_type as projectType,
project_description as projectDescription
from projects
where project_code = projectCode
and sr_user_name = userName;
SET projectCode = projectCode;
SELECT MIN(proj_num) as proj_min_num,
MAX(proj_num) as proj_max_num
from projects
where project_code = projectCode
and sr_user_name = userName;
END