CRUD General options

The CRUD general options can be used to add the CRUD procedures that stand for the CREATE, READ, UPDATE, and DELETE statements. In SQL, they refer to Insert, Select, Update, and Delete.

The topic includes the following sections:

CRUD Select
CRUD Insert
CRUD Update
CRUD Delete

On the General page, you can configure the following options:

Select whether to include the CRUD procedures in the script.
Set the order of columns either By Ordinal Number or Alphabetically.

By default, all options are enabled, and By Ordinal Number is selected.

To access the CRUD options, select CRUD > General in the Options dialog.

CRUD General options

After you have customized a CRUD template, you can generate a CRUD script for the table. To do this, in Object Explorer, right-click the required table and select SQL Complete > Script Table as > specific CRUD procedure. This will generate a script matching the customized CRUD procedure in a new SQL document.

CRUD Select

The CRUD Select options allow users to generate customizable and reusable code templates of the Select procedure for any table. The templates can be executed using the SELECT statement to retrieve data or a set of records based on the primary key specified in the input parameter. By default, the name of the CRUD procedure ends with the name of the operation - usp_$table$_Select.

The Select template contains placeholders for variables that can be replaced with the actual values:

$columns$ is a list of columns from which you want to retrieve data.
$schema$ is a schema to which the source table belongs.
$table$ is a table from which you want to get data.
$where$ is 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 checkbox.

For more information about the template parameters and options, see Generate CRUD procedures for a table.

To access the CRUD Select, select CRUD > Select in the Options dialog.

CRUD Select options

CRUD Insert

The CRUD Insert options allow users to generate customizable and reusable code templates of the Insert procedure for any table. The templates can be executed using the INSERT statement to insert columns and values into the table. By default, the name of the CRUD procedure ends with the name of the operation - usp_$table$_Insert.

The Select template contains placeholders for variables that can be replaced with the actual values:

$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 you want to insert into the columns.
$where$ is a condition based on which the rows will be returned.

If you want to return the results set of the inserted row, select the Return inserted row checkbox.

For more information about the template parameters and options, see Generate CRUD procedures for a table.

To access the CRUD Insert, select CRUD > Insert in the Options dialog.

CRUD Select options

CRUD Update

The CRUD Update options allow users to generate customizable and reusable code templates of the Update procedure for any table. The templates can be executed using the UPDATE statement to modify the data in the table. By default, the name of the CRUD procedure ends with the name of the operation - usp_$table$_Update.

The Update template contains placeholders for variables that can be replaced with the actual values:

$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 executed.
$assignments$ is a list of columns to be modified.
$where$ is a condition based on which the rows will be filtered.

If you want to return the updated row, select the Return updated row checkbox.

For more information about the template parameters and options, see Generate CRUD procedures for a table.

To access the CRUD Update, select CRUD > Update in the Options dialog.

CRUD Select options

CRUD Delete

The CRUD Delete options allow users to generate customizable and reusable code templates of the Delete procedure for any table. The templates can be executed using the DELETE statement to remove rows from the table. By default, the name of the CRUD procedure ends with the name of the operation - usp_$table$_Delete.

The Delete template contains placeholders for variables that can be replaced with the actual values: