Applies to
Summary
This article describes how to set and use the Create CRUD procedures feature.
Description
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:
This will generate code in a new query tab. The generated code will look like this:
When the above code is executed the four procedures will be created under the Stored Procedure folder:
The CRUD feature is located under the CRUD tab of the Options dialog with four sub-tabs for each of CRUD procedure templates (Select, Insert, Update, Delete):
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:
|
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):
The auto-generated name for the CRUD procedure will be like this (crud_EmployeeSelect):
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:
The procedure name for the Select statement will look like this:
If the Insert Select CRUD procedure check box is unchecked:
A procedure for the Select statement will be excluded from the procedures generation:
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:
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:
-
%InsertColumnValues%
Generate parameters for the values which will be passed to an insert statement for the Insert CRUD procedure:
-
%UpdateColumnList%
Listed all columns for the chosen table in the update CRUD procedures:
FAQs
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:
<Drive>:\Users\<Current_user>\AppData\Local\ApexSQL\ApexSQLComplete\Options.xml
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
