執行更新的儲存過程的理念：“粒度”更新方法是否足夠？

我正在著手進行一個大型項目，並且一般對儲存過程進行詢問。

假設我有一張User桌子。

UserKey IDENTITY NOT NULL PRIMARY KEY
FirstName nvarchar(50)NOT NULL
LastName nvarchar(50) NOT NULL
Email nvarchar(50) NOT NULL
Birthdate date NULL
EffectiveFrom datetimeoffset NOT NULL
EffectiveThru datetimeoffset NULL

實際上，這裡還有更多欄位，但為了簡潔起見，我將其略讀到基礎知識。

現在，我面臨創建儲存過程以允許此表的基本 CRUD 功能。

Insert sproc 很簡單。

usp_INSERT_User:

CREATE PROCEDURE [dbo].[usp_INSERT_User]
(
   --@UserKey BIGINT,
   @FirstName NVARCHAR(50),
   @LastName NVARCHAR(50),
   @Email NVARCHAR(50),
   @BirthDate DATE = NULL,
   @EffectiveFromDtTm DATETIMEOFFSET(7),
   @EffectiveThruDtTm DATETIMEOFFSET(7) = NULL,
)

我的主要問題是我應該如何編寫更新儲存過程。

由於可以進行非常精細的更新，我是否應該為每個精細更新創建一個更新儲存過程？

例如，假設我只想在為使用者處理經過驗證的電子郵件更新後更新使用者的電子郵件，我會寫一個帶有一個參數的儲存過程嗎？

usp_UPDATE_UserEmail @NewEmail

如果使用者更新了他們的個人資料，我可以寫一個像這樣的 sproc：

usp_UPDATE_User @UserKey, @FirstName, @LastName, @Email, @Birthdate

因此，為了涵蓋我最常見的更新操作，我最終可能會使用一些儲存過程來涵蓋User表更新的案例。顯然，表越大和/或業務需求的數量越多，將與為特定表寫入的更新儲存過程的數量直接相關。這讓我開始思考大量儲存過程的可維護性。

昨天，我走進了兔子洞

“哦，我為什麼不只寫一個可以通過 XML 參數獲取我想更新的任何參數的最終更新儲存過程”

幸運的是，在不使用動態 sql 的情況下，我能夠創建那個怪物。它有效。

然後我決定測試我喜歡的 xml sproc 和具有非常相似更新的正常更新 sproc（具有多個參數）之間的速度。

因此，我創建了一個包含 1200 萬條記錄的模擬使用者表，並測試了這兩種方法。不出所料，XML 方法平均花費的時間是普通更新儲存過程的 7 倍。

在將參數序列化為 XML 的另一端還有額外的成本，這可能與大批量更新有關。

現在，我說了所有這些來結束並詢問您的經驗中哪種類型的方法最常見（如果有的話）？usp_UPDATE_Field1最常見的做法是擁有像// OR這樣的粒狀儲存usp_UPDATE_Field2過程usp_UPDATE_Field3&4 usp_UPDATE_whateveryouwant

我喜歡擁有一個黃金儲存過程的簡單性，它可以接受你想要傳遞的許多參數並相應地更新它們，但也存在性能缺陷。

提供更多背景資訊：執行 SQL Server 2019。我對這些東西也很陌生。

謝謝你的幫助！

儲存過程是數據庫的程式 API，是您呼叫以改變系統狀態的方法。因此，我更喜歡讓 SP 的名稱反映所需的業務成果，而不是實現 - change_of_address 而不是 update_customer。每個 SP 都具有執行該業務更改的參數，而沒有其他參數。每個人都做一件事並且做得很好。

如果業務支持兩個都更新公共列的流程，那就這樣吧。該列是每個 SP 的輸入。假設有一個基於網路的“更改個人資料”和一個僅限移動設備的“註冊新手機”。他們都寫信給customer.phone_number。對於每個業務目的，我都會有單獨的 SP。

擁有許多小型、緊密集中的儲存過程沒有可測量的成本。下次重新編譯時會自動更改數據類型。對列名的更改是簡單的搜尋和替換。SSMS 的內置依賴分析也可以提供幫助。如果您使用鏡像列的局部變數，則使用列的名稱，例如 customer_name 被讀入@old_customer_name。

我個人不明白為什麼人們編寫只執行 CRUD 的儲存過程。對我來說，SQL 是數據庫的介面。只需為列值編寫一個帶有佔位符的更新語句，然後將其連同綁定的變數一起發送到數據庫。

有人會爭論 SQL 注入，但是 a) 你只是在移動可能發生的點（儲存過程仍然可能寫得不好），並且 b) 你仍然需要防範其他形式的注入（例如惡意使用者在文本欄位中輸入 HTML 和 JavaScript）。

引用自：https://dba.stackexchange.com/questions/286279