A stored procedure is a collection of SQL statements saved in the database and executed as a single unit. It improves performance, security, and code reusability.

  • Accept input parameters
  • Perform logic (SELECT / INSERT / UPDATE / DELETE)
  • Return results or output parameters

1.Why Use Stored Procedures?

✔ Faster execution (precompiled)
✔ Reusable SQL logic
✔ Better security (grant access without table access)
✔ Easier maintenance
✔ Reduce network traffic

2.Basic Syntax

CREATE PROCEDURE procedure_name
AS
BEGIN
SQL statements
END;

3.Create a Simple Stored Procedure

Example Table: Employees

EmployeeID Name Department Salary
1 John IT 50000
2 Jane HR 45000

Stored Procedure

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
SELECT * FROM Employees;
END;

Execute the Procedure

EXEC GetAllEmployees;

4.Stored Procedure with Input Parameters

CREATE PROCEDURE GetEmployeeByDepartment
@Department VARCHAR(50)
AS
BEGIN
SELECT *
FROM Employees
WHERE Department = @Department;
END;

Execute

EXEC GetEmployeeByDepartment 'IT';

5.Stored Procedure with Multiple Parameters

CREATE PROCEDURE GetEmployeeBySalary
@MinSalary INT,
@MaxSalary INT
AS
BEGIN
SELECT *
FROM Employees
WHERE Salary BETWEEN @MinSalary AND @MaxSalary;
END;

6.Stored Procedure with INSERT

CREATE PROCEDURE AddEmployee
@Name VARCHAR(50),
@Department VARCHAR(50),
@Salary INT
AS
BEGIN
INSERT INTO Employees (Name, Department, Salary)
VALUES (@Name, @Department, @Salary);
END;

Execute

EXEC AddEmployee 'Mike', 'IT', 60000;

7.Stored Procedure with UPDATE

CREATE PROCEDURE UpdateEmployeeSalary
@EmployeeID INT,
@NewSalary INT
AS
BEGIN
UPDATE Employees
SET Salary = @NewSalary
WHERE EmployeeID = @EmployeeID;
END;

8.Stored Procedure with DELETE

CREATE PROCEDURE DeleteEmployee
@EmployeeID INT
AS
BEGIN
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID;
END;

Categorized in:

Stored Procedures,