How to create and use CRUD stored procedures in SQL Server

Working with a database, at some point, usually means working with CRUD operations. Although not complex, CRUD operations are often used with SQL, so it is of great importance for developers to learn about how to create them efficiently and easily.

The CRUD abbreviation stands for Create, Read, Update and Delete:

• Create procedures execute and Insert statement to insert a new record in a table.
• Read procedures read values from a table via a Select statement
• Update procedures change data by executing an Update statement on the table based on the primary key specified in the Where clause.
• Delete procedures delete a record based the row specified in the Where clause.

Why use CRUD stored procedures?

There are a couple of reasons why one might use stored procedures for performing CRUD stored procedures instead of using ad-hoc SQL statements:

Performance

When executing a stored procedure, the execution plan of the stored procedure is stored in the SQL Server procedure cache and it can be reused whenever the stored procedure is called.

Whenever an SQL statement is executed in SQL Server, the relational engine first looks at the procedure cache to check that an existing execution plan for that the SQL statement already exists. If it exists, the execution plan will be reused, in that way save the overhead of parsing, optimization, and repeating steps for the SQL statement.

Decoupling SQL code from the application

By removing SQL statements from the application code, all the SQL can be kept in the database and only stored procedure can be used in the application. This decouples the database from the application which offers the advantage is that permissions can be managed directly on the stored procedures which improves security (see more next). It can also significantly decrease the complexity of the client application. This approach also empowers specialists in database performance and DBAs to contribute to ensuring performance is tuned, as they would have more ready and direct access to the SQL Server code in the database itself.

Preventing SQL injection attacks

Using the stored procedures instead of the SQL statements to build dynamic queries reduces the potential for SQL injection attacks from user input data because everything placed into a parameter gets quoted in the process.

Naming conventions for the CRUD stored procedures

Naming conventions for CRUD procedures are often implemented differently from the naming conventions of other stored procedures. Some recommendations are as follows:

• Use a different prefix for CRUD stored procedures from those used for standard stored procedures (e.g. crud_)
• Use the name of the table after prefix for which the CRUD stored procedure is created (e.g crud_AddressType)
• Use the name of the CRUD operation at the end of the CRUD procedure name (e.g. a name for the CRUD update stored procedure should end with Update word. The name for Update stored procedure should look like this: crud_AddressTypeUpdate)

Outline

This article will explain:

• How to create CRUD stored procedures via SQL Server Management Studio (SSMS)
  Here, how to create, read, update and delete stored procedure for specific table by typing the code in a query editor will be shown.
• How to create CRUD stored procedures via Visual Studio
  This will show how to create CRUD stored procedures for specific a table by using the TableAdapter Configuration Wizard window.
• How to create CRUD stored procedures using the ApexSQL Complete’s Create CRUD procedures feature
  It will explain how to create the templates for CRUD stored procedures for any table under the CRUD tab of the Options window in ApexSQL Complete and how to generate code for all four CRUD stored procedures in a query editor with a single click.

As an example, the AdventureWorks2014 database and the AddressType table for creating CRUD stored procedures will be used.

How to create and use CRUD stored procedures via SQL Server Management Studio (SSMS)

Create a stored procedure

The Create stored procedure inserts a new record into the table by using the Insert statement. Every column from the table has its own parameter:

IF OBJECT_ID('Person.crud_AddressTypeInsert') IS NOT NULL
BEGIN
	DROP PROCEDURE Person.crud_cInsert
END
GO
CREATE PROCEDURE Person.crud_AddressTypeInsert
	(
		@Name [nvarchar](50),
		@rowguid [uniqueidentifier],
		@ModifiedDate [datetime]
	)
AS
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	BEGIN TRANSACTION

	INSERT INTO Person.AddressType
	(
		Name, rowguid, ModifiedDate
	)
	VALUES
	(
		@Name,
		@rowguid,
		@ModifiedDate

	)
DECLARE @AddressTypeID INT;
SET @AddressTypeID = SCOPE_IDENTITY()

	SELECT AddressTypeID = @AddressTypeID, 
Name = @Name, 
rowguid = @rowguid, 
ModifiedDate = @ModifiedDate
	FROM Person.AddressType
	WHERE (AddressTypeID = @AddressTypeID)

	COMMIT
GO

The SCOPE_IDENTITY() function (SET @AddressTypeID = SCOPE_IDENTITY()) catches the last inserted identity value into a column.

When executes this code, it will create the crud_AddressTypeInsert procedure for insert values into the AddressType table:

Now, when executes the crud_AddressTypeInsert procedure, it will insert values into AddressType table and return the newly inserted values in the Result grid:

Read stored procedure

This procedure reads the values from the table based on the primary key specified in the input parameter:

IF OBJECT_ID('crud_AddressTypeRead') IS NOT NULL
BEGIN 
    DROP PROC crud_AddressTypeRead
END 
GO
CREATE PROC crud_AddressTypeRead
    @AddressTypeID int
AS 
BEGIN 
 
	SELECT AddressTypeID, Name, rowguid, ModifiedDate
	FROM Person.AddressType
	WHERE (AddressTypeID = @AddressTypeID)
END
GO

The following code:

EXECUTE dbo.crud_AddressTypeRead
        @AddressTypeID = 3;

Will return all records form the AddressType table that contains value 3 in the AddressTypeID column:

Update stored procedure

This procedure updates the table based on the primary key that is specified in the Where clause:

IF OBJECT_ID('crud_AddressTypeUpdate') IS NOT NULL
BEGIN
	DROP PROC
		crud_AddressTypeUpdate;
END; 
GO

CREATE PROC crud_AddressTypeUpdate
	@AddressTypeID INT,
	@Name NVARCHAR(50),
	@rowguid UNIQUEIDENTIFIER,
	@ModifiedDate DATETIME
AS
	BEGIN

		UPDATE Person.AddressType
		SET
			Name = @Name,
			rowguid = @rowguid,
			ModifiedDate = @ModifiedDate
		WHERE AddressTypeID = @AddressTypeID;
	END;
GO

Delete stored procedure

This procedure deletes the records based on the primary key for a record specified in Where clause:

IF OBJECT_ID('crud_AddressTypeDelete') IS NOT NULL
BEGIN
	DROP PROC crud_AddressTypeDelete;
END; 
GO

CREATE PROC crud_AddressTypeDelete
	@AddressTypeID INT
AS
	BEGIN
		DELETE FROM Person.AddressType
		WHERE AddressTypeID = @AddressTypeID;
	END;
GO

How to create CRUD stored procedures via Visual Studio

To create CRUD stored procedures in Visual Studio by using the TableAdapter Configuration Wizard window, right click on the application folder in Solution Explorer. From the context menu under the Add submenu, choose the New Item command:

Under the Add New Item window, choose the DataSet item and click the Add button:

Right-click in the DataSet.xsd window and from the context menu under the Add submenu, choose the TableAdapter command:

This will open the TableAdapter Configuration Wizard window. In the Which data connection should your application use to connect to the database combo box set the data connection:

If the data connection is not set, click the New Connection button:

This will open the Add Connection window. Choose SQL Server and a database to establish connection and click the OK button.

Once the data connection is provided, click the Next button:

Leave the settings in this window as they are and click the Next button:

On the Choose a Command Type window, select the Create new stored procedures radio button and click the Next button.

In the What data should be loaded into the table? text box, enter a Select statement for the Read stored procedure. In this case, the Select statement for the Person.AddressType table will be entered:

After, the Select statement for the Person.AddressType table is entered, click the Advanced Options button, and under the Advanced Options window, select all three check boxes, click the OK button and then click the Next button:

In this window, enter the name for the Read (Select), Insert, Update, Delete procedures:

Clicking the Preview SQL Script button will open the Preview SQL Script window to show the script that will be executed when press the Finish button:

Close the Preview SQL Script window and click the Next button. On the window below, leave settings as they are and click the Next button:

The last window shows the list of tasks that the wizard has performed. Click the Finish button to create CRUD stored procedures for the Person.AddressType table:

Now, when look at Server Explorer in Visual Studio or at Object Explorer in SSMS the CRUD procedures for the Person.AddressType table will appear under the Stored Procedures folder:

How to create CRUD stored procedures using the ApexSQL Complete Create CRUD procedure feature

The Create CRUD procedures is a feature in ApexSQL Complete, add-in for SSMS and Visual Studio, that creates a SQL script which contains the stored procedures the for Select, Insert, Update, Delete statements for chosen table in just one click.

To create the CRUD stored procedures, simply, in Object Explorer under a database, select a desired table for which the CRUD stored procedures needs to be generated, right-click it and from the context menu choose the Create CRUD procedures command:

This will open a new query editor in which a code will be generated for creating Select, Insert, Update, Delete stored procedures for selected table (e.g. AddressType):

The CRUD features come with four sub-tabs for each of CRUD procedures (Select, Insert, Update, Delete) located under the CRUD tab of the Options window:

The procedure script templates under these sub-tabs are customizable and can be changed. For example, let’s change the name of the Select stored procedures. In the Procedure name box delete the %ProcedureType% variable and put the word Read instead (crud_%TableName%%ProcedureType% to crud_%TableName%Read):

More about available variables in the CRUD templates can be found on the Creating CRUD procedures page.

Now let’s press the OK button to preserve the settings and generate once again CRUD stored procedures script for the AdressType table. In Object Explorer select the AdressType table, right-click and from the context menu, choose the Create CRUD procedures option. As shown below, a new name for the Select stored procedure is generated:

Now, jut press the Execute button from the SQL Editor toolbar to create CRUD stored procedures for the AdressType table:

Once the desired CRUD procedures templates are set under the Select, Insert, Update, and Delete sub-tabs, the only thing that needs to be done in order to create CRUD stored procedures is to select a table for which the CRUD procedure code needs to be generated and from the context menu choose the Create CRUD procedures command.

The script that generates CRUD stored procedures by using ApexSQL Complete CRUD feature can save a great amount of time when repetitive CRUD stored procedures need to be written.

See also:

• Creating and using CRUD stored procedures
• TableAdapter Configuration Wizard
• Creating CRUD procedures