Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    117

    Unanswered: Whats wrong with it?

    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;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bharanidharanit View Post
    Wats wrong with this procedure?
    you're using the semi-colon delimiter (i.e. statement terminator) inside it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Posts
    117
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2008
    Posts
    117
    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$$

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •