我有一張這樣的桌子：
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
我還必須一次為 2 個引數賦值。但是假設我只需要更改 Ticket_Price，我不會更改存盤程序的引數。
uj5u.com熱心網友回復：
我將從更改引數的順序開始，您可以檢查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.
uj5u.com熱心網友回復：
您可以在一次更新中更新多個值：
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;
uj5u.com熱心網友回復：
我不確定你是不是這個意思：
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 演示。
這樣做的好處是，在很長的引數串列中，您只能傳遞所需的引數。
uj5u.com熱心網友回復：
基于@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;
這種呼叫存盤程序的形式有兩個好處：
- 您不必正確設定引數的順序。
- 如果您將來需要添加新引數，則不必更新對存盤程序的每個參考。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/485771.html
上一篇：每次單擊按鈕時如何更改文本？
下一篇：如何在特定資料庫中創建存盤程序