How to generate and use CRUD stored procedures in SQL Server

November 28th, 2019

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.

<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>

DELETE procedures

This procedure will delete a row provided in the WHERE clause of the statement.

<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>

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:

Script Table as CRUD Option

When this is done, a new SQL file will be opened. Here, you can see all CRUD operations for the table.

Generated CRUD Code

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:

Accessing SQL Complete Options

In the Options window that will be opened as a result, go to the CRUD menu and click General:

General CRUD Options

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:

Procedure Name Template

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 read more about this tool here.

Leave a Comment