如果存在 SQL Server 存储过程，则更新，否则插入

Question

我创建了一个本地存储过程（如果存在则更新，否则插入）

当我执行该存储过程时，它无法插入或更新任何数据，SQL Server 显示查询已成功运行。

表：

CREATE TABLE Laptops 
(
    Brand varchar(50),
    Series varchar(50),
    Model varchar(50),
    Gener int,
    Ram int,
    HDD int,
    Processor varchar(50),
    Clock float,
    Graphic_Chip varchar(50),
    Graphic_Memory int,
    Qty int,
    Price int
    PRIMARY KEY (Brand,Series,Model,Gener,Ram,HDD,Processor,Clock,Graphic_Chip,Graphic_Memory)
)

存储过程：

CREATE PROCEDURE Insert_Inventory
(
    @Brand Varchar(50),
    @Series Varchar(50),
    @Model Varchar(50),
    @Gener int,
    @Ram int,
    @HDD int,
    @Processor Varchar(50),
    @Clock float,
    @Graphic_Chip Varchar(50),
    @Graphic_Memory int,
    @Qty int,
    @Price int
)
AS
    IF EXISTS (SELECT * FROM Laptops
               WHERE Brand = @Brand
                 and Series = @Series
                 and Model = @Model
                 and Gener = @Gener 
                 and ram = @Ram 
                 and hdd = @HDD 
                 and Processor = @Processor 
                 and Clock = @Clock 
                 and Graphic_Chip = @Graphic_Chip 
                 and Graphic_Memory = @Graphic_Memory)
    BEGIN
        UPDATE Laptops 
        SET 
            Qty = Qty + @Qty, 
            Price = @Price
        WHERE 
            Brand = @Brand 
            and Series = @Series
            and Model = @Model 
            and Gener = @Gener 
            and ram = @Ram 
            and hdd = @HDD 
            and Processor = @Processor 
            and Clock = @Clock 
            and Graphic_Chip = @Graphic_Chip 
            and Graphic_Memory = @Graphic_Memory
    END
    ELSE
    BEGIN
       INSERT into Laptops 
       VALUES (@Brand, @Series, @Model, @Gener, @Ram, @HDD, @Processor, @Clock, @Graphic_Chip, @Graphic_Memory, @Qty, @Price)
    END

执行

exec Insert_Inventory 'Dell', 'Inspiron', '14', 3, 4, 500, 'Core_i_5', 2.7, 'NVIDIA', 512, 20, 42000

- Khurram Sharif

---

这对我有效：http://sqlfiddle.com/#!3/a34d0/2 - Olivier Jacot-Descombes

Answer 1

你需要将你的存储过程用 BEGIN 和 END 包裹起来，你漏掉了这个步骤。在 Select 语句 中，使用 1 来检查 IF Exist。同时还需要检查有没有未提交的事务。除此之外，你还可以使用 SQL MERGE 语句来进行更新和插入操作。

CREATE PROCEDURE Insert_Inventory
(
    @Brand Varchar(50),
    @Series Varchar(50),
    @Model Varchar(50),
    @Gener int,
    @Ram int,
    @HDD int,
    @Processor Varchar(50),
    @Clock float,
    @Graphic_Chip Varchar(50),
    @Graphic_Memory int,
    @Qty int,
    @Price int
)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Laptops
        WHERE Brand=@Brand
            and Series=@Series
            and Model=@Model
            and Gener=@Gener 
            and ram=@Ram 
            and hdd=@HDD 
            and Processor=@Processor 
            and Clock=@Clock 
            and Graphic_Chip=@Graphic_Chip 
            and Graphic_Memory=@Graphic_Memory
    )
    BEGIN
        UPDATE Laptops set qty=qty+@Qty, Price=@Price
        WHERE Brand=@Brand 
            and Series=@Series
            and Model=@Model 
            and Gener=@Gener 
            and ram=@Ram 
            and hdd=@HDD 
            and Processor=@Processor 
            and Clock=@Clock 
            and Graphic_Chip=@Graphic_Chip 
            and Graphic_Memory=@Graphic_Memory
    END
ELSE
    BEGIN
        INSERT into Laptops Values (@Brand,@Series,@Model,@Gener,@Ram,@HDD,@Processor,@Clock,@Graphic_Chip,@Graphic_Memory,@Qty,@Price)
    END

END

- HaveNoDisplayName

---

谢谢，问题已解决。 - Khurram Sharif

FYI - 一个或多个 Transact-SQL 语句构成了该过程的主体。您可以使用可选的 BEGIN 和 END 关键字来封闭这些语句。请访问 http://msdn.microsoft.com/en-us/library/ms187926.aspx 获取更多信息。 - Pரதீப்

实际上，Khurram发布的原始代码是有效的。BEGIN END 是可选的。 - Olivier Jacot-Descombes