Avatar of dobbinjp
dobbinjp

asked on 

return dummy value from stored procedure if no data (null value) found

I have a stored procedure (code attached) that accepts a variable and selects data from the database. There will be situations where a null value is returned for either PREVIDNUMBER or NEXTIDNUMBER. In these situations, I would like to return a value of '999'.

How should I modify my code to accomplish this?

Thanks!!
CREATE OR REPLACE PROCEDURE LCDS.PROC_GETPREVANDNEXT (
    CURRIDNUMBER IN VARCHAR2,
    ENDLOGMILE   OUT NUMBER,
    PREVIDNUMBER OUT VARCHAR2,
    NEXTIDNUMBER OUT VARCHAR2
)
AS
BEGIN
  select rs_end_log, prev_sect_id, next_sect_id
  into endlogmile, previdnumber, nextidnumber
  from vw_roadsections
  where sect_id = curridnumber;
  RETURN;
END;

Open in new window

Oracle Database

Avatar of undefined
Last Comment
dobbinjp

8/22/2022 - Mon