{{announcement.body}}
{{announcement.title}}

How to Generate and Use CRUD Stored Procedures in SQL Server

DZone 's Guide to

How to Generate and Use CRUD Stored Procedures in SQL Server

In this article, see how to generate and use CRUD stored procedures in SQL Server.

· Database Zone ·
Free Resource

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.

SQL


READ Procedures

The READ procedure retrieves table records based on the primary key provided in the input parameter.

SQL


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.

SQL


DELETE Procedures

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

SQL


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:

Screenshot of dbForge SQL Complete


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

Screenshot of dbForge SQL Complete


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:

Screenshot of dbForge SQL Complete


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

Screenshot of dbForge SQL Complete


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:

Name of 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).

Further Reading

The Top 5 Most Common SQL Server Performance Problems

The Unreasonable Effectiveness of SQL

Topics:
sql ,crud ,database systems ,sql server ,database ,tutorial

Published at DZone with permission of Jordan Sanders . See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}