Creating CRUD procedures

Applies to

ApexSQL Complete


This article describes how to set and use the Create CRUD procedures feature.


This feature allows to automatically create Select, Insert, Update and Delete stored procedures using customizable templates.

To create procedures, right click on a table or database in Object Explorer or Server Explorer and from the context menu chose the Create CRUD procedures command:

Create CRUD procedures command

This will generate code in a new query tab. The generated code will look like this:

generate CRUD code

When the above code is executed the four procedures will be created under the Stored Procedure folder:

Created CRUD procedure

The CRUD feature is located under the CRUD tab of the Options dialog with four sub-tabs for each of CRUD procedure templates (SelectInsertUpdateDelete):

CRUD tab

Note, that the stored procedure script template is customizable and is designed by using named variables, e.g. %ColumnList% that will be replaced with exact object names (schemas, table names, column names…) in the process of generating a specific stored procedure.

All available variables can be manually entered by typing the name of the variable (e.g. %InputParameter%) or from the context menu, by clicking desired variable:

CRUD context menu

Quick tip icon

Quick tip:

Click on the Default template button will reset any changes and return the script to the default (aka factory settings) template for the particular CRUD procedure

By default, the procedure name is created like this: crud_%TableName%%ProcedureType%.

%TableName% stands for the name of the table over which the CRUD procedure is created (e.g. Employee).

By default, %ProcedureType% will be the name of the CRUD sub-tab which is currently used, for example (e.g. Select):

CRUD sub-tab

The auto-generated name for the CRUD procedure will be like this (crud_EmployeeSelect):

Auto-generated name for the CRUD procedure

Let’s set some custom name in the Procedure name text boxes and see how will look like when the code is generated.

In the Procedure name box under the Select tab of the Options window type in prc_%TableName%_Select_all_data:

Custom CRUD name

The procedure name for the Select statement will look like this:

autogenerated name for the select statement

If the Insert Select CRUD procedure check box is unchecked:

The Insert Select CRUD procedure check box

A procedure for the Select statement will be excluded from the procedures generation:

autogenerated CRUD code without Select stateent

Here the list of all available variables:

  • %SchemaName%

    The name of the schema (e.g. dbo)

  • %TableName%

    The name of a table (e.g. Employee)

  • %InputParameter%

    Generates a list of parameters. The name of the parameters will be made up of the names of columns for the selected table. The column name ColumnName_ID the generated parameter name will be @ColumnName_ID

  • %WhereClause%

    Set condition for the particular CRUD procedure.

    For example, for the Insert Delete CRUD procedure:

    The Insert Delete CRUD procedure

    User enters the value (e.g. 1) for the identity column, which row will be deleted:

    EXECUTE Person.crud_AddressTypeDelete
            @AddressTypeID = 1;
  • %ColumnList%

    List all the columns from a chosen table (e.g. ColumnName_ID, ColumnName_1, ColumnName_…n)

  • %ColumnListInsert%

    List all columns from the chosen table in the Insert CRUD procedure:

    The Insert CRUD procedure - %ColumnListInsert%

  • %InsertColumnValues%

    Generate parameters for the values which will be passed to an insert statement for the Insert CRUD procedure:

    The Insert CRUD procedure - %InsertColumnValues%

  • %UpdateColumnList%

    Listed all columns for the chosen table in the update CRUD procedures:

    The Update CRUD procedure - %UpdateColumnList%


Q: Will the new installation or upgrade of ApexSQL Complete delete the custom created CRUD templates?

A: No. The installation or upgrade to a newer version won’t affect custom changes.

Q: Where is information about the CRUD procedures saved?

A: By default, the CRUD procedures are saved in the following location:


Q: Can settings for CRUD procedures be used on a different machine?

A: Yes. The settings for the CRUD procedures on different machines can be used. By copying the Options.xml file to the appropriate location on another machine where ApexSQL Complete is already installed.

  • Note: When copy the Options.xml file to another machine, all ApexSQL Complete settings will be copied too