STORED PROCEDURE in SQL Server

A Stored Procedure consists of a single or set of statements and are stored in a database as an object. A stored procedure can be cashed and reused. From the security point of view, a stored procedure is also good by hiding all the column and details from the user and giving access only for little information.

Types of Stored Procedures

There are two types of stored procedures in SQL Server.

System Stored Procedures

System stored procedures are built in stored procedure in SQL Server Management Studio and are created and executed by SQL Server for administrative activities.

User Defined Stored Procedures

User defined stored procedures are created by developers for database administration. The stored procedures can be parameterized or parameter less. User defined procedures can take input from the user and return the result as per input parameter.

Stored Procedure Demo

In this demo, we will create a table tblEmployee with some records, and then we will see the stored procedure with following scenarios.

Stored procedure with SELECT query and without parameter.
Parameterized stored procedure with SELECT query.
Stored procedure with INSERT query.
Stored Procedure with UPDATE query.
Stored procedure with DELETE query.

Let’s create a table tblEmployee.

CREATE TABLE tblEmployee
(
	EmpID INT PRIMARY KEY NOT NULL,
	EmpFirstName NVARCHAR(20) NOT NULL,
	EmpLastName NVARCHAR(20) NOT NULL,
	EmpAge INT NULL,
	EmpGender NVARCHAR(10) NULL,
	EmpEmail NVARCHAR(50) NULL,
	EmpPhone NVARCHAR(20) NULL,
	EmpSalary INT NULL,
	EmpJoiningDate DATETIME NULL
)
GO

Let’s insert some records in tblEmployee table.

INSERT INTO tblEmployee
    (EmpID, EmpFirstName,EmpLastName,EmpAge,EmpGender,EmpEmail,EmpPhone,EmpSalary,EmpJoiningDate)
VALUES
    (1,'john','dave',26,'male','john@gmail.com','0923044554561',26000,'2010-02-01'),
    (2,'kamran','akmal',35,'male','kami@gmail.com','0923044545562',25000,'2011-01-01'),
    (3,'umar','akmal',36,'male','umar@gmail.com','0923044554563',38000,'2015-01-01'),
    (4,'thomas','hardy',36,'male','thomas@gmail.com','0923044554564',25000,'2016-05-05'),
    (5,'amar','sidhu',36,'male','amar@gmail.com','0923044554560',25000,'2016-05-05'),
    (6,'simran','sidhu',25,'female','simran@gmail.com','0923044554565',33000,'2018-01-01'),
    (7,'diyana','jeni',26,'female','diyana@gmail.com','0923044554566',50000,'2005-01-01'),
    (8,'maria','anders',27,'female','maria@gmail.com','0923044554567',70000,'2004-01-01'),
    (9,'anam','chaudhary',29,'female','anam@gmail.com','0923044554568',25000,'2020-01-01'),
    (10,'Amelia','sidhu',56,'female','amelia@gmail.com','0923044554569',50000,'2015-01-01')
    GO

Let’s see records in tblEmployee table.

Stored Procedure with SELECT query and without parameter

Below is the stored procedure with simple select query and without parameters. First we create the stored procedure and then we call that stored procedure.

CREATE PROCEDURE usp_GetAllEmployees
AS
BEGIN
    SELECT * FROM tblEmployee
END

To execute the stored procedure, we write the name of the stored procedure followed by the EXECUTE or EXEC keyword. By executing the stored procedure, we get the result as below.

Stored Procedure with SELECT query and one parameter

Suppose we want to get all the employees whose gender is male or female. We create the procedure as below.

The stored procedure with parameter will be executed by providing the parameter name and value (where name is optional while value is mandatory) after the name of the procedure as below.

If we provide the @gender=’male’, we get only male records and if we provide @gender=’female’, we get all the female records.

CREATE PROCEDURE usp_GetAllEmployeesByGender
(
    @gender NVARCHAR(10)
)
AS
BEGIN
    SELECT * FROM tblEmployee
    WHERE EmpGender=@gender
END

EXECUTE usp_GetAllEmployeesByGender @gender='male'

Stored Procedure with SELECT query and multiple parameters

Let’s create a procedure with multiple parameters (@gender and @salary). We create the stored procedure as:

CREATE PROCEDURE usp_GetAllEmployeesByGenderAndSalary
(
    @gender NVARCHAR(10),
    @salary INT
)
AS
BEGIN
    SELECT * FROM tblEmployee
    WHERE 
        EmpGender=@gender AND
        EmpSalary>=@salary
END

Now we execute the procedure with multiple parameters as below and see the result.

EXECUTE usp_GetAllEmployeesByGenderAndSalary @gender='male',@salary=25000

Stored Procedure with INSERT query

To insert a row in tblEmployee we write a procedure as.

CREATE PROCEDURE usp_InsertDataIntblEmployee
(
    @EmpID              INT
    @EmpFirstName	NVARCHAR(20),
    @EmpLastName	NVARCHAR(20),
    @EmpAge 	        INT,
    @EmpGender		NVARCHAR(10),
    @EmpEmail		NVARCHAR(50),
    @EmpPhone		NVARCHAR(20),
    @EmpSalary		INT,
    @EmpJoiningDate	DATETIME
)
AS
BEGIN
    INSERT INTO tblEmployee
    	(EmpID,EmpFirstName,EmpLastName,EmpAge,EmpGender,EmpEmail,EmpPhone,EmpSalary,EmpJoiningDate)
    VALUES
	(@EmpID,@EmpFirstName,@EmpLastName,@EmpAge,@EmpGender,@EmpEmail,@EmpPhone,@EmpSalary,@EmpJoiningDate)
END

Execute the above procedure to insert a new record in tblEmployee table.

EXECUTE usp_InsertDataIntblEmployee 11,'adam','smith',35,'male','adam@gmail.com','0923044548754',35000,'2020-06-01'

We can see the inserted record in the table tblEmployee as shown below.

Stored Procedure with UPDATE query

In this demo we will create stored procedure to update EmpFirstName and EmpEmail whose EmpID is 5.

CREATE PROCEDURE usp_UpdateEmployeeRecord
(
    @EmpID          INT,
    @EmpFirstName   NVARCHAR(20),
    @EmpLastName    NVARCHAR(20),
    @EmpEmail	    NVARCHAR(50)
)
AS
BEGIN
    UPDATE tblEmployee 
        SET EmpFirstName=@EmpFirstName,
            EmpLastName=@EmpLastName,
            EmpEmail=@EmpEmail
    WHERE   EmpID=@EmpID
END

Execute the stored procedure usp_UpdateEmployeeRecord and set the EmpFirstName=’Jon’ ,EmpLastName=’smith’ and EmpEmail=’john@gmail.com’ for EmpID=1 and see the result in the below image.

EXECUTE usp_UpdateEmployeeRecord 1,'john','smith','john@gmail.com'

Stored Procedure with DELETE query

Create procedure to delete a record from a table, tblEmployee.

CREATE PROCEDURE usp_DeleteEmployee
(
    @EmpID INT
)
AS
BEGIN
    DELETE FROM tblEmployee
    WHERE EmpID=@EmpID
END

Execute the stored procedure, we deleted the Employee with EmpID=11 and see the result. Now there is no employee whose EmpID is 11.