如何在SQL Server中生成和使用CRUD存儲過程

大多數數據庫系統基于4個最簡單的數據操作操作，這些操作稱為CRUD。該首字母縮寫詞代表創建、讀取、更新和刪除。

在開發和管理數據庫時，可以使用CRUD存儲過程來執行所有與數據有關的任務。這種存儲過程的好處是，一旦編寫了一次，便可以根據需要重復使用多次，而無需每次都編寫新代碼。這是對臨時SQL語句的重大改進，臨時SQL語句應在每次使用它們時重新編寫。

讓我們詳細了解每個CRUD存儲過程。

仔細研究CRUD存儲過程

在繼續之前，我們要說一下CRUD存儲過程的命名。將它們命名為每個過程都包含它們所應用的表的名稱，并且以它們所執行的操作的名稱結束，這通常是一種很好的做法。這樣，為同一表編寫的所有過程將被分組在一起，并且更容易搜索。

但是，它不是強制性的，您可以堅持自己喜歡的任何命名模式。

現在，讓我們看一下第一種過程類型。

創建程序

這些將執行INSERT語句，創建一個新記錄。此類過程應為表的每一列接受一個參數。

IF?OBJECT_ID('Sales.usp_Currency_Insert')?IS?NOT?NULL?BEGIN??????DROP?PROC?Sales.usp_Currency_Insert??END??GO?CREATE?PROC?Sales.usp_Currency_Insert??????@CurrencyCode?NCHAR(3),?????@Name?dbo.Name,?????@ModifiedDate?datetime?AS??????SET?NOCOUNT?ON??????SET?XACT_ABORT?ON??
BEGIN?TRAN
?
INSERT?INTO?Sales.Currency?(CurrencyCode,?Name,?ModifiedDate)
SELECT?@CurrencyCode,?@Name,?@ModifiedDate
?
/*
--?Begin?Return?row?code?block
?
SELECT?CurrencyCode,?Name,?ModifiedDate
FROM???Sales.Currency
WHERE??CurrencyCode?=?@CurrencyCode?AND?Name?=?@Name?AND?ModifiedDate?=?@ModifiedDate
?
--?End?Return?row?code?block
?
*/
COMMIT
GO

讀取程序

READ過程根據輸入參數中提供的主鍵檢索表記錄。

IF?OBJECT_ID('Sales.usp_Currency_Select')?IS?NOT?NULL?BEGIN??????DROP?PROC?Sales.usp_Currency_Select??END?GO?CREATE?PROC?Sales.usp_Currency_Select?????@CurrencyCode?NCHAR(3),?????@Name?dbo.Name?AS?????SET?NOCOUNT?ON??????SET?XACT_ABORT?ON??
BEGIN?TRAN
?
SELECT?CurrencyCode,?Name,?ModifiedDate?
FROM???Sales.Currency
WHERE??CurrencyCode?=?@CurrencyCode?AND?Name?=?@Name??
?
COMMIT
GO

更新程序

這些過程使用WHERE子句中指定的記錄的主鍵在表上執行UPDATE語句。就像CREATE過程一樣，它為每個表列接受一個參數。

<code>IF?OBJECT_ID('Sales.usp_Currency_Update')?IS?NOT?NULL?BEGIN??????DROP?PROC?Sales.usp_Currency_Update?END??GO?CREATE?PROC?Sales.usp_Currency_Update?@CurrencyCode?NCHAR(3),?@Name?dbo.Name,?@ModifiedDate?datetime?AS??????SET?NOCOUNT?ON??????SET?XACT_ABORT?ON??
BEGIN?TRAN
?
UPDATE?Sales.Currency
SET????ModifiedDate?=?@ModifiedDate
WHERE??CurrencyCode?=?@CurrencyCode?AND?Name?=?@Name
?
/*
--?Begin?Return?row?code?block
?
SELECT?ModifiedDate
FROM???Sales.Currency
WHERE??CurrencyCode?=?@CurrencyCode?AND?Name?=?@Name
?
--?End?Return?row?code?block
?
*/
COMMIT
GO</code>

刪除程序

此過程將刪除該語句的WHERE子句中提供的行。

<code>IF?OBJECT_ID('Sales.usp_Currency_Delete')?IS?NOT?NULL?BEGIN??????DROP?PROC?Sales.usp_Currency_Delete?END??GO?CREATE?PROC?Sales.usp_Currency_Delete??@CurrencyCode?NCHAR(3),?@Name?dbo.Name?AS??????SET?NOCOUNT?ON??????SET?XACT_ABORT?ON??
BEGIN?TRAN
?
DELETE
FROM???Sales.Currency
WHERE??CurrencyCode?=?@CurrencyCode?AND?Name?=?@Name
?
COMMIT
GO</code>

使用dbForge SQL Complete生成CRUD過程

在SSMS和Visual Studio中均可使用的dbForge SQL Complete插件，我們只需單擊幾下便可以生成CRUD過程，并具有多種選項，這些選項允許配置這些過程的生成方式。在本文中，我們將使用SSMS展示SQL Complete的功能。

要為一個表生成CRUD過程，右鍵單擊該表，轉到SQL Complete菜單，然后單擊Script Table as CRUD：

完成此操作后，將打開一個新的SQL文件。在這里，您可以看到該表的所有CRUD操作。

更改CRUD生成設置

要配置dbForge SQL Complete如何生成CRUD，您首先需要轉到窗口頂部的SQL Complete菜單，然后單擊“選項”：

結果將在“選項”窗口中打開，轉到“CRUD”菜單，然后單擊“常規”：

在此選項卡中，您可以指定CRUD生成過程中包括哪些過程，并指定要使用的哪個列的順序——按字母順序還是按序號順序。

您也可以通過在CRUD菜單中選擇相應的選項來分別配置每個過程。首先，您可以手動更改生成的過程的名稱：

接下來，每個過程都有唯一的選項。

對于SELECT，如果輸入參數為null，則有一個Return all data復選框。

對于INSERT，可以指定是否在完成后返回插入的行。

類似的選項可用于UPDATE –它使您可以選擇是否要返回更新的行。

沒有其他用于DELETE的唯一選項。

最后，對于每個過程，都有“代碼模板”部分。在本節中，您可以更改指定過程的代碼的生成方式。在代碼模板中，以$ name $格式提供了參數（例如$ schema $或$ columns $）。通過更改這些參數，可以修改所生成過程的代碼。

結論

如您所見，使用CRUD命令實現和管理數據操作過程比使用臨時SQL語句更為可取，并且可以借助dbForge SQL Complete插件輕松完成此操作。