我创建了一个本地存储过程(如果存在则更新,否则插入)
当我执行该存储过程时,它无法插入或更新任何数据,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