This feature will automatically create stored procedures for SELECT, INSERT, UPDATE and DELETE procedures using a customizable template. The CRUD generator will be able to create stored procedures for a single table or an entire database.
A user has the possibility to enter procedure name in the form of: prefix + TableName + suffix for every type of CRUD stored procedure (e.g. SELECT, INSERT, UPDATE and DELETE):
The stored procedure script template is customizable and is designed by using named variables that will be replaced with exact object names (schemas, table names, column names…) in the process of generating a specific stored procedure.
The CRUD tab will be located in the ApexSQL Complete Options window and will have four sub-tabs for each of CRUD procedure templates (Select, Insert, Update, Delete):
In the field prefix the user can enter the prefix which will be in the name of the procedure like (crud).
The TableName stands for the name of the table in which the CRUD generating process is performed, for example table ErrorLog.
In the suffix field, the suffix can be entered which will be in the end of the procedure name, by default, the name of the suffix will be the name of the CRUD sub-tab which the user is currently using, for example (Insert).
The final name for the procedure will be:
By default, checkboxes for including specified procedure (eg. Include Insert procedure) are checked. A user has the possibility to uncheck it, then, procedure is not included into the process of generation.
Clicking the OK button will save all changes in template for CRUD procedures. Clicking the Default button will return default templates for CRUD procedures.
The principle of settings for generating the stored procedures is the same for the other three sub-tabs.
In the image below, it is shown how generated procedure for the Insert statement will look like:
By right clicking in the Object Explorer window, in database or table in the drop down menu, the CRUD procedures feature will be available.
The execution of this feature will create a script with CRUD stored procedures for the selected database or table. The procedure template will be filled in with real meta data from the selected database/table. Scripts with CRUD stored procedures will be opened in a new query tab and ready for execution.
March 16, 2017