1. What is a Stored Procedure?
A Stored Procedure in SQL is a precompiled set of SQL statements that is stored in the database and can be executed whenever needed.
It helps to reuse code, improve performance, and increase security.
In simple words:
A Stored Procedure is like a function inside the database that you can call again and again.
Example use:
- Insert data
- Update data
- Delete data
- Complex queries
- Business logic
2. Why use Stored Procedures?
| Benefit | Explanation |
|---|---|
| Performance | Precompiled → runs faster |
| Reusability | Write once, use many times |
| Security | Hide table logic from users |
| Easy Maintenance | Change in one place |
| Less Network Traffic | Only call procedure |
3. Syntax of Stored Procedure (SQL Server)
CREATE PROCEDURE ProcedureName
AS
BEGIN
SQL Statements
END
Example:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT * FROM Employees
END
Run procedure:
EXEC GetEmployees
4. Stored Procedure with Parameters
We can pass values to stored procedures.
CREATE PROCEDURE GetEmployeeById
@Id INT
AS
BEGIN
SELECT * FROM Employees WHERE Id = @Id
END
Execute:
EXEC GetEmployeeById 5
5. Stored Procedure with Multiple Parameters
CREATE PROCEDURE GetEmployee
@Id INT,
@Name VARCHAR(50)
AS
BEGIN
SELECT *
FROM Employees
WHERE Id = @Id OR Name = @Name
END
Run:
EXEC GetEmployee 1, 'Rahul'
6. Stored Procedure for INSERT
CREATE PROCEDURE AddEmployee
@Name VARCHAR(50),
@Salary INT
AS
BEGIN
INSERT INTO Employees(Name, Salary)
VALUES(@Name, @Salary)
END
Run:
EXEC AddEmployee 'Amit', 30000
7. Stored Procedure for UPDATE
CREATE PROCEDURE UpdateSalary
@Id INT,
@Salary INT
AS
BEGIN
UPDATE Employees
SET Salary = @Salary
WHERE Id = @Id
END
Run:
EXEC UpdateSalary 1, 50000
8. Stored Procedure for DELETE
CREATE PROCEDURE DeleteEmployee
@Id INT
AS
BEGIN
DELETE FROM Employees
WHERE Id = @Id
END
Run:
EXEC DeleteEmployee 1
9. Stored Procedure with IF ELSE
CREATE PROCEDURE CheckSalary
@Salary INT
AS
BEGIN
IF @Salary > 50000
PRINT 'High Salary'
ELSE
PRINT 'Low Salary'
END
10. Stored Procedure with TRY CATCH
CREATE PROCEDURE SafeInsert
@Name VARCHAR(50)
AS
BEGIN
BEGIN TRY
INSERT INTO Employees(Name)
VALUES(@Name)
END TRY
BEGIN CATCH
PRINT 'Error Occurred'
END CATCH
END
11. Difference Between Stored Procedure and Function
| Feature | Stored Procedure | Function |
|---|---|---|
| Return value | Optional | Must return |
| Can use INSERT/UPDATE | Yes | Limited |
| Can return table | Yes | Yes |
| Can use TRY CATCH | Yes | No |
| Call | EXEC | SELECT |
12. When to Use Stored Procedure
Use stored procedure when:
- Complex query
- Repeated query
- Business logic in DB
- Need security
- Need better performance
13. Real Life Example
Login system
CREATE PROCEDURE LoginUser
@Email VARCHAR(100),
@Password VARCHAR(100)
AS
BEGIN
SELECT *
FROM Users
WHERE Email = @Email
AND Password = @Password
END
Run:
EXEC LoginUser 'test@gmail.com', '1234'
14. Conclusion
Stored Procedures are very important in SQL because they:
- Improve performance
- Make code reusable
- Increase security
- Reduce errors
- Used in real projects
