Automatically creating SQL Server CRUD procedures

This video shows how to automatically create SQL Server CRUD procedures using the Create CRUD procedures feature in ApexSQL Complete.

The Create CRUD procedures feature can create fully customizable, template-based CRUD procedures for a particular table quickly and efficiently without typing any code.

Transcript

Hello, and welcome to this video presentation on how to automatically create and use CRUD procedures in ApexSQL Complete.

This feature allows for automatically creating select, insert, update and delete stored procedures using customizable templates.

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

This will generate code in a new query tab and the generated code will look like similar to this:

Now, when this code is executed, the four procedures; Select, Insert, Update, Delete for the chosen table will be created:

The CRUD procedure feature is located under the CRUD tab of the Options window of ApexSQL Complete with four sub-tabs for each of CRUD procedure templates (Select, Insert, Update, Delete):

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

All available variables can be manually entered by typing the name of the variable for example %InputParameter% or from the context menu, by clicking on the desired variable:

The %SchemaName% variable is for the name of the schema, for example dbo).

The %InputParameter% variable generates a list of parameters. The name of the parameters will be made up of the names of columns for the selected table.

The %WhereClause% variable sets a condition for the particular CRUD procedure. For example, for the delete CRUD procedure, a user enters the value, like 1, for the identity column for which row will be deleted:

The %ColumnList% variable lists all the columns from a chosen table.

The %ColumnListInsert% variable lists all columns from the chosen table in the Insert CRUD procedure:

The %InsertColumnValues% variable generates parameters for the values which will be passed to an insert statement for the Insert CRUD procedure:

The %UpdateColumnList% variable lists all columns for the chosen table in the update CRUD procedures:

By clicking the Default template button, the default settings for the particular CRUD procedure will be reset, overwriting any changes that have been made.

The procedure name, by default, is created with the “crud_” prefix followed with the %TableName% variables which is a placeholder for the name of the table based in which the CRUD procedure is created, for example Address Type. For the suffix, there is the %ProcedureType% variable which will be replaced with the name of the CRUD sub-tab which the user is currently using, Select in this case.

Just for illustrative, purposes, let’s delete the default value in the Procedure name box and enter Select_all_data and press OK button:

Now, let’s generate some code to see results of our settings.

Select a table, EmailAddress in this example, under the Object Explore, right-click and choose the Create CRUD procedures option:

As you can see, the name for the Select procedure is Select_all_data while other names for CRUD procedures for that table will stay the same:

Now, when this script is executed, the CRUD procedures will be created for the EmailAddress table:

If you want to exclude a crud procedure type, for example Selects, from CRUD procedure generation, uncheck the Include Select CRUD procedure check box:

Now when the Create CRUD procedures option is used, the Select procedure will not be generated for the chosen table:

With the Creating CRUD procedures option from ApexSQL Complete, you can create fully customizable, template-based CRUD procedures for a particular table quickly and efficiently without typing any code.

Thanks for watching. For more information, please visit apexsql.com