4
Reply

how to insert/update data in two tables using single stored procedure

ajay raju

ajay raju

Dec 22 2010 4:03 AM
7.8k


 hi 
 
    i am writing a one single stored procedure fot insert and update a data into two tables. and i am getting one error. below is my code.
CREATE PROCEDURE sp_Emp_Save
(
 @Eno INT,
 @Ename VARCHAR(100),
 @JOb VARCHAR(100),
 @Gender CHAR(1),
 @Sno INT,
 @Sname VARCHAR(100)
)
 AS
   BEGIN
        IF NOT EXISTS (SELECT Ename,Job FROM EMP WHERE Eno = @Eno)
             INSERT INTO EMP (Eno,Ename,Job,Gender) VALUES (@Eno,@Ename,@Job,@Gender)
             INSERT INTO Class (Sno,Sname) VALUES (@Sno,@Sname)
        ELSE
             UPDATE EMP SET Ename = @Ename, Job = @Job, Gender = @Gender WHERE Eno = @Eno
             UPDATE CLASS SET Sname = @Sname WHERE Sno = @sno
     END 
and i am getting the below error:
Msg 156, Level 15, State 1, Procedure sp_Test_Save, Line 15
Incorrect syntax near the keyword 'ELSE'.
please tell me , where was i write the wrong. and please give a solution for this.
Thanks.

Answers (4)