SQL Server 存储过程 IF Exist Update Else Insert

SQL Server Stored Procedure IF Exist Update Else Insert

提问by Khurram Sharif

I created a stored procedure locally (IF Exist Update Else Insert)

我在本地创建了一个存储过程（IF Exist Update Else Insert）

When I execute the procedure it can't insert or update any data, SQL Server shows that query has run successfully.

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

Table:

桌子：

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)
)

Stored procedure:

存储过程：

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

Execute

执行

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

回答by HaveNoDisplayName

You have to wrap your SP into BEGIN& END, you are missing that. And also use 1 when check IF Existin Select Statement.And also check that, Some transaction is open but not commit. Instead of Doing this also, You can use SQL MERGE to Update and Insert Statement.

你必须把你的 SP 包装成BEGIN& END，你错过了。，还可以使用1当检查IF Exist中Select Statement。而且还要检查，有些交易是开放的，但不承诺。您可以使用 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