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.
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.
Stored Procedures are not too difficult to use, and I hope this article has helped you understand them better.