How to Generate and Use CRUD Stored Procedures in SQL Server

Most database systems operate on the basis of 4 of the 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.

You might also want to read: Collection: SQL Server Sample Databases

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.

xxxxxxxxxx

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.

xxxxxxxxxx

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.

xxxxxxxxxx

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. This can be done easily with the help of the dbForge SQL Complete add-in. However, working with CRUD is not its only functionality (and by a long stretch).