Most database systems operate on the basis of 4 simplest data manipulation operations that are called by the acronym CRUD. This acronym stands for Create, Read, Update, and Delete.
When developing and managing databases, you can use CRUD stored procedures to perform all data-related tasks. The benefit of such stored procedures is that, once they’re written once, they can be reused as many times as required, with no need to write new code each time. This is a great improvement over ad hoc SQL statements which should be written anew every time we use them.
Let’s look at each CRUD stored procedure in detail.
A closer look at CRUD stored procedures
Before moving forward, there’s one thing we want to say about naming CRUD stored procedures. It is usually a good practice to name them in such a way that each procedure contains the name of the table they’re applied to and also ends with the name of the operation they’re performing. This way, all procedures written for the same table will be grouped together and are much easier to search through.
However, it’s not mandatory at all and you can stick to any
naming pattern you prefer.
Now, let’s look at the first
procedure type.
CREATE procedures
These will execute an INSERT statement, creating a new record. Such procedures should accept one parameter for each column of the table.
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 procedures
The READ procedure retrieves table records based on the primary key provided in the input parameter.
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
UPDATE procedures
These procedures use the primary key for a record specified in the WHERE clause to execute an UPDATE statement on a table. Just like CREATE procedures, it accepts one parameter for each table column.
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
DELETE procedures
This procedure will delete a row provided in the WHERE clause of the statement.
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
Generating CRUD procedures using dbForge SQL Complete
Using the dbForge SQL Complete add-in that works both in SSMS and Visual Studio, we can generate CRUD procedures in a few clicks, with a variety of options that allow configuring how exactly these procedures are generated. In this article, we’ll use SSMS to show SQL Complete’s functionality.
To generate CRUD procedure for a table, right-click the table, go to the SQL Complete menu, and click Script Table as CRUD:
When this is done, a new SQL file will be opened. Here, you can see all CRUD operations for the table.
Changing CRUD generation settings
To configure how dbForge SQL Complete generates CRUD, you would first need to go to the SQL Complete menu at the top of the window and click Options:
In the Options window that will be opened as a result, go to the CRUD menu and click General:
In this tab, you can specify which procedures include in the CRUD generation process and specify which column order to use – alphabetical or by ordinal number.
You can also configure each procedure separately by choosing the corresponding option in the CRUD menu. First of all, you can manually change the name of the generated procedures:
Next, there are options unique to each procedure.
For SELECT, there is a Return all data if input parameters are null checkbox.
For INSERT, you can specify whether to return the inserted row upon completion.
A similar option is available for UPDATE – it allows you to choose whether you want the updated row to be returned.
There is no additional unique option for DELETE.
Finally, for each procedure, there is the Code template section. In this section, you can change how the code of the specified procedure is generated. In code templates, there are parameters provided in the format $name$ (for example, $schema$ or $columns$). By changing these parameters, you can modify the code of the generated procedure.
Conclusion
As you can see, implementing and managing the data manipulation process using CRUD commands is much more preferable to using ad hoc SQL statements, and this can be done easily with the help of the dbForge SQL Complete add-in. You can check its functionality for yourself by downloading a free trial version of the tool. Working with CRUD is not its only functionality (and by a long stretch), so you can click the banner below to learn more about it.
