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:"

Answer 1

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;