Working with SQL databases and tables usually implies performing daily data-related tasks using the CRUD operations in order to re-use, manipulate, and access data later. For example, you can create a new table, modify, populate the table with data, retrieve and store data for future use, or delete if needed.
To expedite the routine tasks, SQL Complete provides code templates for the CRUD operations that can be brought up to your needs.
The article covers how to create custom CRUD procedure templates in SQL Server with the help of SQL Complete.
Introduction
CRUD is an acronym that stands for CREATE, READ, UPDATE, and DELETE. In SQL Server, CRUD is represented by 4 operations performed on the selected data against a specific SQL database:
- CREATE refers to inserting columns and values into the table.
- READ refers to retrieving data from the table.
- UPDATE refers to modifying data in the table.
- DELETE refers to deleting data and records in the table.
Generating the CRUD Procedures in SQL Complete
To begin with, we will review how SQL Complete generates the CRUD procedures. On the main menu, navigate to SQL Complete and click Options on the shortcut menu. In the dbForge SQL Complete: Options window that opens, switch to the CRUD tab under which the CRUD procedure templates (Select, Insert, Update, and Delete) are located.
On the General tab, you can use the following options:
- Select whether to include either of the CRUD procedures in the script.
- Arrange the order of columns either by ordinal number or alphabetically.
- Click Reset Page Defaults to overwrite the changes you apply and set them to the default settings. This option is also available on the Select, Insert, Update, and Delete tabs.
Each code template for the Select, Insert, Update, and Delete procedures is customizable and contains placeholders for variables that can be replaced with the actual value. By default, the name of the CRUD procedure ends with the name of the operation you are using.
From now on, SQL Complete supports the –region and –endregion options that are automatically generated for the CRUD code templates. The tool allows setting a name for the region and expanding or collapsing them in the CRUD procedures:
Let’s explore in-depth each of the CRUD operations.
CREATE CRUD – Insert SQL Server Procedure
The Insert procedure can be used to add new rows and their values to the table. If you want to copy data into a target table from other SQL tables, use an INSERT INTO SELECT statement. In this case, the data types of both tables should match; otherwise, it returns the error.
On the CRUD menu, switch to the Insert tab to view the code template for the procedure.
Instead of the variable placeholders, you should specify the following information:
- $schemas$ is the name of the schema to which the table belongs.
- $table$ is the name of the table for which the CRUD procedure is created.
- $columns$ is a list of columns you want to insert.
- $values$ is a list of values to be inserted into the columns.
- $where$ is a search condition by which the rows to be returned are filtered.
If you want to return the results set of the inserted row, select the Return inserted row check box.
READ CRUD – Select SQL Server Procedure
The Select statement can be used to retrieve data or a set of records from the table based on the primary key within the input parameter.
On the CRUD menu, switch to the Select tab to view how SQL Complete generates the procedure.
In the Select statement, you can replace the following placeholders:
- $columns$: Define the columns from which you want to retrieve data.
- $schema$: Specify a schema to which the source table belongs.
- $table$: Set a table from which you want to get data.
- $where$: (Optional) Set a condition by which the results set will be filtered.
If you want to get all records with input parameters that equal NULL, select the Return all data if input parameters are null check box.
UPDATE CRUD – Update SQL Server Procedure
The Update statement can be used to modify the data in the table. The code template is located under the CRUD > Update tab.
In the Update statement, you need to specify a schema and a table for which you want to execute the CRUD operation. The statement includes the SET and WHERE clauses where you need to indicate columns to be modified and define the filters for the specific rows respectively.
If you want to return the updated row, select the Return updated row check box.
DELETE CRUD – Delete SQL Server Procedure
The Delete statement removes the row or rows specified in the WHERE clause of the statement.
On the CRUD > Delete tab, view the code template for the Delete statement.
The Delete statement contains the FROM and WHERE clauses:
- In the FROM clause, replace the %schema$ and $table$ placeholders with the name of the source schema and table for which the rows should be deleted.
- In the WHERE clause, replace $where$ with a condition by which the rows to be deleted will be filtered.
Generating a CRUD Procedure
After you have created a custom CRUD template, you can generate a CRUD script for the table. To generate a table script as CRUD, in Object Explorer, right-click the table you need and select SQL Complete > Script Table as CRUD on the shortcut menu. A new SQL document opens displaying the script matching the CRUD template you have created.
Conclusion
With SQL Complete, you can easily modify the code template for the CRUD procedures. The tool automatically adds the name of the operation you are working on and wraps the procedure code into named regions.
To evaluate all the useful features SQL Complete provides, download a free 30-day trial version of the tool.
Watch Tutorial
For more information about the key features of dbForge SQL Complete, take a look at our video tutorial:
