Creating CRUD Stored Procedures in SQL

Introduction

Create, Read, Update and Delete operations are pivotal for any database application. These DML (Data Manipulation Language) statements can become risky to do, especially if you are new to databases and do not know how these statements work.

Today you will learn about CRUD operations as well as how to put them into Stored Procedures so that the data manipulation is on the database side and not executed from the application.

Stored Procedures

As the name implies: A Stored Procedure is a procedure that is stored. In layman’s terms this means that it is a sequence of code or a block of SQL code that is stored and that can be used and run over and over without interfering with your application.

A Stored Procedure can also accept parameters from an outside source (such as an application) and act accordingly based on the passed value or values.

Creating a simple Stored Procedure in T-SQL

The following is a very basic example of a Stored Procedure:

USE DatabaseName;  
GO  
CREATE PROCEDURE StoredProcedureName  
AS   
    SELECT FirstName, LastName, Gender  
    FROM Employees  
    WHERE FirstName = 'Hannes' AND LastName = 'du Preez'  
GO  

You specify which Database to use using the USE SQL Statement. CREATE PROCEDURE identifies the Stored Procedure that you want to create. What follows the AS statement is the code for the Stored Procedure. In the above example (impractical, I know, but it is to demonstrate the concept) is a simple SQL query that selects three fields from the Employees table where the condition in the WHERE clause is met. A more advanced example of a Stored Procedure follows:

USE DatabaseName;  
GO  
CREATE PROCEDURE StoredProcedureName  
    @Variable1 nvarchar(50),   
    @Variable2 nvarchar(50)   
AS   
    SELECT FirstName, LastName, Gender  
    FROM Employees 
    WHERE FirstName = @FirstName AND LastName = @LastName  
GO  

Here I have included the use of parameters (Variable1 and Variable2). They accept information from an outside source and then the query makes use of it and produces the desired output. This method of creating Stored Procedures is quite common as developers use C#, VB.NET or other programming languages to send the information to the query and make use of its results in DataSet or DataTable objects.

Changing a Stored Procedure

You make use of the ALTER PROCEDURE Statement to update changes to a Stored Procedure. For Example:

ALTER PROCEDURE StoredProcedureName  
    @Variable1 VARCHAR(50),   
    @Variable2 INT
AS   
    SELECT FirstName, LastName, Age  
    FROM Employees 
    WHERE FirstName = @FirstName AND LastName = @LastName  
GO  

Renaming a Stored Procedure

You rename an existing Stored Procedure with the use of the built-in sp_rename T-SQL command. Here is a small example:

EXEC sp_rename 'OldName', 'NewName';

EXEC executes the Stored Procedure.

Delete a Stored Procedure

You delete a Stored Procedure by using the DROP Statement. An example follows:

DROP PROCEDURE ProcedureName;
GO

Creating an INSERT Stored Procedure

You create a Stored Procedure that inserts information into a database in the following way:

CREATE PROCEDURE InsertTest
@FirstName VARCHAR(50), 
@LastName VARCHAR(50), 
@Age INT
AS
INSERT INTO Employee
           ([FirstName]
           ,[LastName]
           ,[Age])
     VALUES
           (@FirstName
           ,@LastName
           ,@Age)
GO

The InsertTest Stored Procedure accepts two variable character strings (FirstName and LastName) and an Integer (Age) as parameters. Whatever is fed into them will be inserted into the Employee database.

Creating an Update Stored Procedure

An Update Stored Procedure may look similar to the following:

CREATE PROCEDURE UpdateTest
@FirstName VARCHAR(50), 
@LastName VARCHAR(50), 
@Age INT
AS
UPDATE Employee
    SET [FirstName] = @FirstName, 
           ,[LastName] = @LastName
           ,[Age] = @Age
GO

The Employee Table’s fields get Updated with the information present in the parameters.

Creating a DELETE Stored Procedure

The following example demonstrates how to do a DELETE operation inside a Stored Procedure:

CREATE PROCEDURE DeleteTest
@FirstName VARCHAR(50), 
@LastName VARCHAR(50), 
@Age INT
AS
   DELETE Employee  
    WHERE FirstName = @FirstName AND LastName = @LastName
GO

DeleteTest deletes the record where the condition is met.

Conclusion

Stored Procedures are not too difficult to use, and I hope this article has helped you understand them better.