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.

Recommended Readings
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL