用于插入和更新的存储过程 [英] stored procedure for insert and update

问题描述

您好，先生，

我正在编写一个用于在两个表中插入和更新数据的存储过程.我收到一个错误.以下是我的代码:

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.

推荐答案

您的IF-ELSE中没有 BEGIN-END 子句.

请查看以下知识基础 [ ^ ]带有示例.

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

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

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

您需要添加BEGIN END子句.

试试这个，

You need to add BEGIN END clause.

Try this,

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

这篇关于用于插入和更新的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！