How to generate CRUD procedures for a table

SQL Server manipulates data with the help of the CRUD operations that stand for the CREATE, READ, UPDATE, and DELETE statements. SQL Complete generates customizable and reusable CRUD procedures for any particular table that can be executed in the following way:

CREATE refers to the INSERT statement used to insert columns and values into the table.
READ refers to the SELECT statement used to retrieve data from the table.
UPDATE refers to the UPDATE statement used to modify data in the table.
DELETE refers to the DELETE statement used to remove data and records from the table.

To create a CRUD procedure, in Object Explorer, right-click on the database table and select SQL Complete > Script Table as CRUD. This will generate a code in a new SQL document.

For example, SQL Complete will generate the following CRUD procedures for the Production.ProductSubcategory table.

It’s possible to configure options for adding the CRUD procedures. To learn how to configure the general options for CRUD operations in SQL Complete, see CRUD General options.

Procedure for selecting data from a table

USE AdventureWorks2019;
GO

IF OBJECT_ID('Production.usp_ProductCategory_Select') IS NOT NULL
BEGIN 
    DROP PROC Production.usp_ProductCategory_Select 
END
GO
CREATE PROC Production.usp_ProductCategory_Select
    @ProductCategoryID int,
    @Name dbo.Name,
    @rowguid uniqueidentifier
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    BEGIN TRAN

    SELECT ProductCategoryID, Name, rowguid, ModifiedDate
    FROM   Production.ProductCategory
    WHERE  ProductCategoryID = @ProductCategoryID AND Name = @Name AND rowguid = @rowguid 

    COMMIT
GO

Procedure for inserting data into a table

IF OBJECT_ID('Production.usp_ProductCategory_Insert') IS NOT NULL
BEGIN 
    DROP PROC Production.usp_ProductCategory_Insert
END 
GO
CREATE PROC Production.usp_ProductCategory_Insert
    @ProductCategoryID int,
    @Name dbo.Name,
    @rowguid uniqueidentifier,
    @ModifiedDate datetime
AS 
    SET NOCOUNT ON
    SET XACT_ABORT ON

    BEGIN TRAN

    INSERT INTO Production.ProductCategory (ProductCategoryID, Name, rowguid, ModifiedDate)
    SELECT @ProductCategoryID, @Name, @rowguid, @ModifiedDate

    /*
    -- Begin Return row code block

    SELECT ProductCategoryID, Name, rowguid, ModifiedDate
    FROM   Production.ProductCategory
    WHERE  ProductCategoryID = @ProductCategoryID AND Name = @Name AND rowguid = @rowguid AND ModifiedDate = @ModifiedDate

    -- End Return row code block

    */
    COMMIT
GO

Procedure for updating data in a table

IF OBJECT_ID('Production.usp_ProductCategory_Update') IS NOT NULL
BEGIN 
    DROP PROC Production.usp_ProductCategory_Update
END
GO
CREATE PROC Production.usp_ProductCategory_Update
@ProductCategoryID int,
@Name dbo.Name,
@rowguid uniqueidentifier,
@ModifiedDate datetime
AS 
    SET NOCOUNT ON
    SET XACT_ABORT ON

    BEGIN TRAN

    UPDATE Production.ProductCategory
    SET    ModifiedDate = @ModifiedDate
    WHERE  ProductCategoryID = @ProductCategoryID AND Name = @Name AND rowguid = @rowguid

    /*
    -- Begin Return row code block

    SELECT ModifiedDate
    FROM   Production.ProductCategory
    WHERE  ProductCategoryID = @ProductCategoryID AND Name = @Name AND rowguid = @rowguid

    -- End Return row code block

    */
    COMMIT
GO

Procedure for deleting data from a table

IF OBJECT_ID('Production.usp_ProductCategory_Delete') IS NOT NULL
BEGIN 
    DROP PROC Production.usp_ProductCategory_Delete
END 
GO
CREATE PROC Production.usp_ProductCategory_Delete
@ProductCategoryID int,
@Name dbo.Name,
@rowguid uniqueidentifier
AS 
    SET NOCOUNT ON
    SET XACT_ABORT ON

    BEGIN TRAN

    DELETE
    FROM   Production.ProductCategory
    WHERE  ProductCategoryID = @ProductCategoryID AND Name = @Name AND rowguid = @rowguid

    COMMIT
GO

Manage CRUD operation templates

By default, the name generation of stored procedures is based on the usp_TableName_Operation template, which can be modified in the Options window. To access the Options window, on the SQL Complete menu, select Options and switch to the CRUD tab.

On the CRUD > General tab, you can do the following:

Select whether to include the Insert, Select, Update, or Delete procedures into the SQL script.
Set the column order either by ordinal number or alphabetically.

On the CRUD > Select tab, you can do the following:

Specify the Select procedure name and customize a code template.
Retrieve all records with input parameters being equal to NULL by selecting the Return all data if input parameters are null check box.

On the CRUD > Insert tab, you can do the following:

Enter the Insert procedure name and customize a code template.
Select Return inserted row check box.

On the CRUD > Update tab, you can do the following:

Specify the Update procedure name and customize a code template.
Select the Return updated row check box.

On the CRUD > Delete tab, you can do the following:

Enter the procedure name and customize a code template.

On all CRUD tabs, you can overwrite the changes you apply and set them to the default settings by clicking Reset Page Defaults. SQL Complete automatically wraps the CRUD procedures into named regions.

Placeholders

SQL Complete provides the following predefined placeholders for CRUD procedures, which deliver multiple replacement points for the same parameter:

Placeholder	Description
$schema$	Name of the schema.
$procedure$	Name of the procedure.
$parameters$	List of parameters.
$columns$	List of columns.
$table$	Name of the table for which the CRUD procedure is generated.
$where$	Condition to filter the rows returned.
$values$	List of values to be inserted into columns.
$assignments$	Column values assigned to the parameters.
`$if(!select)$/*$endif$`	When enabled, returns a selection of inserted or updated rows. You can enable/disable this feature with the checkboxes Return inserted row and Return updated row.
$schema; format="id"$	Attribute renderer for the schema.
$procedure; format="id"$	Attribute renderer for the procedure.

Want to Find out More?

Download a free 14-day trial