Procedure to return the greater value

[jayjaybigs]

Hello All,

I have a procedure like the below:

DROP PROCEDURE SP_GM_GET_HND_ID!

CREATE
PROCEDURE sp_gm_get_hnd_id (
OUT grt_hndID VARCHAR(20)
)
LANGUAGE SQL
BEGIN
DECLARE handID bigint;

-- get the next hnd_id

SELECT MAX(id)+1 INTO handID FROM gm_hnd;

-- get the next stg_hnd_id
SELECT MAX(id)+1 INTO handID FROM stg_gm_hnd;

SET hndID = char(handID);
SET stg_hndID = char(handID);

END

I my aim is to choose and return the greater value from hndID and stg_hndID. Then output the greater falue into OUT grt_hndID VARCHAR(20). Please help.

 

[PHV]

You may something try this:
SELECT MAX(MaxID)+1 INTO handID FROM (
SELECT MAX(id) MaxID FROM gm_hnd
UNION SELECT MAX(id) FROM stg_gm_hnd) U;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886