Whats wrong with it?

bharanidharanit · #1 (**permalink**) 04-10-11, 01:57

Hi,
Wats wrong with this procedure? It showing errors on declare, end and end if

Code:

CREATE PROCEDURE MIS_SP_Register_Employee(
    IN _Emp_Name VARCHAR(30),
    IN _Username VARCHAR(8),
    IN _Userpwd  VARCHAR(40),
    IN _Salt VARCHAR(3),
    IN _FK_Role_ID INT,
    OUT _Emp_ID  INT)
BEGIN
    DECLARE OUT_Emp_ID INT;
    DECLARE CheckExists INT;
    SET CheckExists = 0;
  SELECT COUNT(*) INTO CheckExists FROM Employee_Master WHERE Emp_ID = _Emp_ID;
  IF (CheckExists > 0) THEN 
    INSERT INTO Employee_Master(Emp_Name,Username,Userpwd,Salt,FK_Role_ID) VALUES(_Emp_Name,_Username,_Userpwd,_Salt,_FK_Role_ID);
    SELECT OUT_Emp_ID = LAST_INSERT_ID();
  END IF;
END;

r937 · #2 (**permalink**) 04-10-11, 04:43

Quote:

Originally Posted by bharanidharanit

Wats wrong with this procedure?

you're using the semi-colon delimiter (i.e. statement terminator) inside it

bharanidharanit · #3 (**permalink**) 04-10-11, 13:16

Hi thankyou,
still i am getting the error near 2nd declare

Code:

CREATE PROCEDURE MIS_SP_Register_Employee(
    IN _Emp_Name VARCHAR(30),
    IN _Username VARCHAR(8),
    IN _Userpwd  VARCHAR(40),
    IN _Salt VARCHAR(3),
    IN _FK_Role_ID int,
    OUT _Emp_ID  int)
BEGIN
    DECLARE OUT_Emp_ID INT 
    DECLARE CheckExists INT
    SET CheckExists = 0
  SELECT COUNT(*) INTO CheckExists FROM Employee_Master WHERE Emp_ID = _Emp_ID
  IF CheckExists > 0 THEN 
    INSERT INTO Employee_Master(Emp_Name,Username,Userpwd,Salt,FK_Role_ID) VALUES(_Emp_Name,_Username,_Userpwd,_Salt,_FK_Role_ID)
    SET OUT_Emp_ID = LAST_INSERT_ID()
  END IF
END

r937 · #4 (**permalink**) 04-10-11, 15:00

you asked what was wrong, i pointed you to your poor choice of delimiter, you removed the delimiter altogether... sorry, that was the wrong answer

have you seen the mysql manual page on stored procedures?

do a search on "mysql delimiter"

bharanidharanit · #5 (**permalink**) 04-19-11, 12:23

hi thankyou, i had done.

Code:

DELIMITER $$
CREATE PROCEDURE MIS_SP_Register_Employee(
    IN _Emp_Name VARCHAR(30),
    IN _Username VARCHAR(8),
    IN _Userpwd  VARCHAR(40),
    IN _Salt VARCHAR(3),
    IN _FK_Role_ID int,
    OUT _Emp_ID  int)
BEGIN
    DECLARE OUT_Emp_ID INT 
    DECLARE CheckExists INT
    SET CheckExists = 0
  SELECT COUNT(*) INTO CheckExists FROM Employee_Master WHERE Emp_ID = _Emp_ID
  IF CheckExists > 0 THEN 
    INSERT INTO Employee_Master(Emp_Name,Username,Userpwd,Salt,FK_Role_ID) VALUES(_Emp_Name,_Username,_Userpwd,_Salt,_FK_Role_ID)
    SET OUT_Emp_ID = LAST_INSERT_ID()
  END IF
END$$

Internet Services

Web Development

Digital Marketing

Consumer Tech