stored procedure for insert and update

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi sir,

I am writing a single stored procedure for inserting and updating data into two tables. 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

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 sir, where was I writing wrong code? Please give me a solution for this.

Thanks.

Posted 21-Dec-10 21:59pm

ajay.raju531

Updated 21-Dec-10 22:03pm

JF2015

v2

Add a Solution

Comments

JF2015 22-Dec-10 4:03am

Edited to fix spelling errors.

5 solutions

Solution 4

You need to add BEGIN END clause.

Try this,

SQL

BEGIN
        IF NOT EXISTS (SELECT Ename,Job FROM EMP WHERE Eno = @Eno)
           BEGIN
             INSERT INTO EMP (Eno,Ename,Job,Gender) VALUES (@Eno,@Ename,@Job,@Gender)
             INSERT INTO Class (Sno,Sname) VALUES (@Sno,@Sname)
           END
        ELSE
           BEGIN
             UPDATE EMP SET Ename = @Ename, Job = @Job, Gender = @Gender WHERE Eno = @Eno
             UPDATE CLASS SET Sname = @Sname WHERE Sno = @sno
           END
     END

Posted 21-Dec-10 22:08pm

Toniyo Jackson

Solution 5

SQL

BEGIN
        IF NOT EXISTS (SELECT Ename,Job FROM EMP WHERE Eno = @Eno)
           BEGIN
             INSERT INTO EMP (Eno,Ename,Job,Gender) VALUES (@Eno,@Ename,@Job,@Gender)
             INSERT INTO Class (Sno,Sname) VALUES (@Sno,@Sname)
           END
        ELSE
           BEGIN
             UPDATE EMP SET Ename = @Ename, Job = @Job, Gender = @Gender WHERE Eno = @Eno
             UPDATE CLASS SET Sname = @Sname WHERE Sno = @sno
           END
     END

Posted 27-Dec-10 17:14pm

Amit chirutkar

Solution 6

hi, just u see the below link.. because, i already posted...

How to SELECT and UPDATE in one query using Different Tables in sql server?[^]

below the Code:
--------

SQL

IF  EXISTS  (SELECT 1   FROM    InsCarrier  WHERE   (InsCarrierId = @InsCarrierId))
        UPDATE  InsCarrier
        SET     UserId = @UserId,InsCarrierName=@InsCarrierName
        WHERE   (InsCarrierId = @InsCarrierId)
    ELSE
        INSERT  INTO    InsCarrier  (UserId,InsCarrierName)
                        VALUES      (@B2bUserId,@InsCarrierName

Posted 27-Dec-10 17:23pm

velmahesh

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2010-12-21T22:05:00

Solution 1

There is no BEGIN-END clause in your IF-ELSE.

Have a look at the following knowledgebase [^]with example.

Posted 21-Dec-10 22:05pm

Sandeep Mewara

Comments

ajay.raju531 22-Dec-10 4:25am

Thank you

Dalek Dave 27-Dec-10 23:22pm

Good Link.

**JF2015** · Accepted Answer · 2010-12-21T22:07:00

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)
          BEGIN
             INSERT INTO EMP (Eno,Ename,Job,Gender) VALUES (@Eno,@Ename,@Job,@Gender)
             INSERT INTO Class (Sno,Sname) VALUES (@Sno,@Sname)
          END
        ELSE
          BEGIN
             UPDATE EMP SET Ename = @Ename, Job = @Job, Gender = @Gender WHERE Eno = @Eno
             UPDATE CLASS SET Sname = @Sname WHERE Sno = @sno
          END
     END