SQL Topics
Create Procedure
title: Create Procedure
In the previous lesson, you learned what a Stored Procedure is and why it is used in database systems.
A Stored Procedure allows developers to store a set of SQL statements inside the database and execute them whenever needed.
Now let's learn how to create a Stored Procedure.
Creating procedures is one of the most important skills in SQL because it allows you to automate repetitive tasks, centralize business logic, improve security, and enhance performance.
What is CREATE PROCEDURE?
The CREATE PROCEDURE statement is used to create and store a procedure inside the database.
Once created:
Procedure Stored In Database
↓
Can Be Executed Anytime
↓
Reusable Multiple TimesInstead of writing the same SQL statements repeatedly, you can simply execute the procedure.
Basic Syntax
General syntax:
CREATE PROCEDURE ProcedureName
AS
BEGIN
SQL Statements
END;Understanding the Syntax
CREATE PROCEDURECreates a new procedure.
ProcedureNameSpecifies the name of the procedure.
BEGINMarks the start of procedure code.
ENDMarks the end of procedure code.
Creating Sample Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Insert sample records:
INSERT INTO Employees VALUES
(1,'Rahul',50000),
(2,'Priya',60000),
(3,'Amit',70000);Creating Your First Procedure
Example:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;Purpose:
Retrieve All EmployeesProcedure Structure
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;Explanation:
Procedure Name:
GetEmployees
Operation:
Retrieve Employee RecordsProcedure for Selecting Data
Example:
CREATE PROCEDURE GetHighSalaryEmployees
AS
BEGIN
SELECT *
FROM Employees
WHERE Salary > 60000;
END;Purpose:
Retrieve Employees
With Salary Above 60000Procedure for Inserting Data
Example:
CREATE PROCEDURE AddEmployee
AS
BEGIN
INSERT INTO Employees
VALUES
(
4,
'Neha',
80000
);
END;Purpose:
Insert Employee RecordProcedure for Updating Data
Example:
CREATE PROCEDURE IncreaseSalary
AS
BEGIN
UPDATE Employees
SET Salary = Salary + 5000;
END;Purpose:
Increase SalariesProcedure for Deleting Data
Example:
CREATE PROCEDURE DeleteEmployee
AS
BEGIN
DELETE FROM Employees
WHERE EmployeeID = 4;
END;Purpose:
Delete EmployeeMultiple Statements in a Procedure
A procedure can contain multiple SQL statements.
Example:
CREATE PROCEDURE EmployeeOperations
AS
BEGIN
UPDATE Employees
SET Salary = Salary + 1000;
SELECT *
FROM Employees;
END;Purpose:
Update Salaries
Then Display DataProcedure with Transaction
Example:
CREATE PROCEDURE TransferBonus
AS
BEGIN
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = Salary + 5000
WHERE EmployeeID = 1;
COMMIT;
END;Purpose:
Perform Transaction SafelyNaming Conventions
Good Names:
GetEmployees
CreateOrder
GenerateInvoice
CalculateSalaryPoor Names:
Test1
ABC
DataProcUse meaningful names.
Creating Procedures in MySQL
MySQL syntax:
DELIMITER //
CREATE PROCEDURE GetEmployees()
BEGIN
SELECT *
FROM Employees;
END //
DELIMITER ;Creating Procedures in SQL Server
SQL Server syntax:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;Creating Procedures in PostgreSQL
PostgreSQL syntax:
CREATE PROCEDURE GetEmployees()
LANGUAGE SQL
AS $$
SELECT *
FROM Employees;
$$;Real-World Example: Banking
Procedure:
Create Customer
Create Account
Generate Account NumberAll logic stored inside one procedure.
Real-World Example: E-Commerce
Procedure:
Create Order
Update Inventory
Generate InvoiceExecuted using one procedure call.
Real-World Example: Payroll System
Procedure:
Calculate Salary
Deduct Tax
Generate PayslipAutomated inside database.
Real-World Example: Hospital Management
Procedure:
Register Patient
Assign Doctor
Generate AppointmentCentralized business logic.
Advantages of CREATE PROCEDURE
Code Reusability
Write once, execute many times.
Better Security
Users can execute procedures without direct table access.
Faster Execution
Procedures are often precompiled.
Easier Maintenance
Business logic stays in one location.
Reduced Network Traffic
Only procedure calls are sent.
Common Errors
Duplicate Procedure Name
Example:
CREATE PROCEDURE GetEmployeeswhen procedure already exists.
Result:
Error
Procedure Already ExistsMissing BEGIN-END Block
Incorrect structure may fail.
Syntax Errors
Missing semicolons or keywords.
Invalid Table Names
Procedure references non-existent tables.
Best Practices
Use Meaningful Names
Reflect procedure purpose.
Keep Procedures Small
Avoid excessively large procedures.
Use Comments
Document procedure behavior.
Validate Inputs
Prevent incorrect data.
Test Thoroughly
Verify all execution paths.
Common Interview Questions
What is CREATE PROCEDURE?
A SQL statement used to create a stored procedure.
Where is a Stored Procedure stored?
Inside the database.
Can a procedure contain multiple SQL statements?
Yes.
Why use Stored Procedures?
For reusability, security, and performance.
Can procedures modify data?
Yes.
They can perform:
INSERT
UPDATE
DELETE
SELECToperations.
Summary
The CREATE PROCEDURE statement is used to create reusable SQL programs inside a database. Stored procedures centralize business logic, improve security, reduce code duplication, and enhance database performance.
In this lesson, you learned:
- What CREATE PROCEDURE is
- Procedure syntax
- Creating procedures
- SELECT procedures
- INSERT procedures
- UPDATE procedures
- DELETE procedures
- Database-specific syntax
- Real-world examples
- Best practices
Mastering CREATE PROCEDURE is essential because stored procedures are widely used in enterprise applications to automate and manage complex database operations.
Next Step
Continue to the next lesson:
Parameters in Procedure →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Create Procedure.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, stored, procedures
Related SQL Topics