如何使用存储过程在T-SQL中更新多个值

Question

我有一个这样的表:

CREATE TABLE Ticket
(
id_Ticket int PRIMARY KEY,
id_Passengers int,
id_Schedule int,
Ticket_Price money,
Nomer_Mest int
)

声明和执行:

DECLARE @Ticket_Price int, @id_Ticket int;
SET @Ticket_Price = 50 
SET @id_Ticket = 9
EXEC AlterTicket @Ticket_Price, @id_Ticket

这是我的存储过程:

CREATE PROCEDURE AlterTicket
@Ticket_Price Money, @id_Ticket int, @Nomer_Mest int;
AS
UPDATE Ticket 
SET Ticket_Price = @Ticket_Price
WHERE id_Ticket = @id_Ticket
UPDATE Ticket 
SET Nomer_Mest = @Nomer_Mest 
WHERE id_Ticket = @id_Ticket

我还必须一次为两个参数赋值。但是我们说我只需要改变Ticket_Price，我不会改变的参数存储过程

Answer 1

我将从改变参数的顺序开始，您可以检查NULL:

CREATE PROCEDURE AlterTicket
@id_Ticket int, @Nomer_Mest int, @Ticket_Price Money
AS
UPDATE Ticket 
SET Ticket_Price = COALESCE(@Ticket_Price, Ticket_Price), Nomer_Mest = COALESCE(@Nomer_Mest, Nomer_Mest) 
WHERE id_Ticket = @id_Ticket;

调用:

EXEC AlterTicket @id_Ticket, @Nomer_Mest, NULL;

或:

EXEC AlterTicket @id_Ticket, NULL, @Ticket_Price;

正如我在评论中所说的，我也强烈建议考虑使用decimal而不是money。

Answer 2

您可以在一次更新中更新多个值:

CREATE PROCEDURE AlterTicket
@Ticket_Price Money, @id_Ticket int, @Nomer_Mest int;
AS
UPDATE Ticket 
SET Ticket_Price = @Ticket_Price, Nomer_Mest = @Nomer_Mest
WHERE id_Ticket = @id_Ticket;

Answer 3

我不确定你是不是这个意思:

CREATE PROCEDURE AlterTicket
@id_Ticket int, @Ticket_Price Money = null, @Nomer_Mest int = null
AS
begin
if @Ticket_Price is not null and @Nomer_mest is not null
UPDATE Ticket 
SET Ticket_Price = @Ticket_Price, Nomer_Mest = @Nomer_Mest
WHERE id_Ticket = @id_Ticket;
else if @Ticket_Price is not null
UPDATE Ticket 
SET Ticket_Price = @Ticket_Price
WHERE id_Ticket = @id_Ticket;
else if @Nomer_Mest is not null
UPDATE Ticket 
SET Nomer_Mest = @Nomer_Mest
WHERE id_Ticket = @id_Ticket;
end

这是DBFiddle的演示。

这样做的好处是，在一个很长的参数列表中，您可以只传递需要的参数。

Answer 4

基于@HoneyBadger的工作，我将在您的存储过程定义中使用默认值，然后调用它的赋值方法，例如

CREATE PROCEDURE AlterTicket
(
@id_Ticket int
, @Nomer_Mest int = null
, @Ticket_Price Money = null
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
UPDATE Ticket SET
Ticket_Price = COALESCE(@Ticket_Price, Ticket_Price)
, Nomer_Mest = COALESCE(@Nomer_Mest, Nomer_Mest) 
WHERE id_Ticket = @id_Ticket;
RETURN 0;
END;

调用:

EXEC AlterTicket @id_Ticket = @id_Ticket, @Nomer_Mest = @Nomer_Nest;

或:

EXEC AlterTicket @id_Ticket = @id_Ticket, @Ticket_Price = @Ticket_Price;

这种形式调用存储过程有两个好处:

1. 你不必把参数的顺序弄对。
2. 如果将来需要添加新参数，则不必更新对存储过程的每个引用。