Having multiple SQL statements within a STORED PROCEDURE returning values
Posted by: naren gokal
Date: March 11, 2012 03:21PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Having multiple SQL statements within a STORED PROCEDURE returning values
1702
March 11, 2012 03:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.