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.
Join the DZone community and get the full member experience.Join For Free
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
These will execute an INSERT statement, creating a new record. Such procedures should accept one parameter for each column of the table.
The READ procedure retrieves table records based on the primary key provided in the input parameter.
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.
This procedure will delete a row provided in the WHERE clause of the statement.
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.
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).
Published at DZone with permission of Jordan Sanders. See the original article here.
Opinions expressed by DZone contributors are their own.