Building logic inside a stored procedure to check validity of employee id

Question

I need to develop a database stored procedure with an input parameter for employee_id to the stored procedure and two output parameters. One for return code and another for return message.

CREATE OR REPLACE PROCEDURE CHECK_ID (emp_id IN INT,
                                    out_status OUT INT,
                                    out_msg OUT VARCHAR
                                     )
AS 
BEGIN

DECLARE
emp_salary INTEGER;

BEGIN
  SELECT SALARY INTO emp_salary FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;
  IF EMPLOYEE_ID = emp_id 
  THEN
  out_status := 1;
  out_msg:= 'Employee is valid and his total salary is :'|| emp_salary; 
  ELSE 
  out_status :=0; 
  --out_msg := 
  DBMS_OUTPUT.PUT_LINE('Employee Number' || emp_id || 'is not valid') ;

END IF;

END;

If the employee number is valid, return code 1 is to be returned and return message as: "Employee is valid and his total salary is : "

If not return code needs to be -1 and the return message as "Employee Number is not valid:"

You should tag your question with the database you are using. It is obviously Oracle, so I added that tag. — Gordon Linoff, May 04 '16 at 01:35
There is absolutely no reason to SHOUT here. We're all quite good at reading, and SHOUTING will not get your question answered sooner. Typing your question title in ALL CAPS is rude and extremely annoying. Please stop now. — Ken White, May 04 '16 at 01:35
The code is giving me an error I can't figure out:Error(25,4): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge — Sidney Bookeart, May 04 '16 at 02:20

score 0 · Answer 1 · answered May 04 '16 at 04:16

You cannot compare column values to a variable value without fetching the the value of the column in a variable (EMPLOYEE_ID = emp_id). Further, there are other syntax errors in your code. Try this one.

CREATE OR REPLACE PROCEDURE check_id 
(
emp_id     IN  INTEGER,
out_status OUT INTEGER,
out_msg    OUT VARCHAR2
)
AS 
  emp_salary INTEGER;
  emp_found  BOOLEAN;

BEGIN

   emp_found := FALSE;

   BEGIN

     SELECT salary INTO emp_salary 
     FROM   employees
     WHERE  employee_id = emp_id;

     emp_found := TRUE;

   EXCEPTION
    WHEN NO_DATA_FOUND THEN
       emp_found := FALSE;      
   END;

  IF emp_found = TRUE THEN
     out_status := 1;
     out_msg:= 'Employee is valid and his total salary is : '|| emp_salary; 
  ELSE 
     out_status := 0; 
     out_msg := 'Employee Number ' || emp_id || ' is not valid';
  END IF;
END;

Here is how you can test it. Replace the first parameter with the employee number.

DECLARE
  v_status INTEGER;
  v_msg    VARCHAR2(1000);
BEGIN
  check_id (1,v_status,v_msg);
  DBMS_OUTPUT.PUT_LINE(v_status || ' ' || v_msg);
END;

Building logic inside a stored procedure to check validity of employee id

1 Answers1