What is a Stored Procedure in SQL?

A Stored Procedure is a precompiled set of SQL statements stored in the database that can be executed to perform tasks like data manipulation, queries, and transaction handling.

What is the difference between a Stored Procedure and a Function?

A Stored Procedure can perform multiple operations, return multiple values, and does not need to return a value. A Function must return a single value and is often used in queries.

Why use Stored Procedures and Functions?

They improve performance by reducing repeated SQL parsing, enhance security through controlled access, and make code reusable and easier to maintain.

Stored Procedure and Function Interactive Demo

Master database operations with Fullstackgada's fun approach! 🗄️

More Diagrams

Follow for More

👥 Accounts Table

Account ID	Name	Balance	Type

💼 Employees Table

Employee ID	Name	Salary	Performance

📊 Transaction History

Transaction ID	From Account	To Account	Amount	Status	Timestamp

💰 Transfer Money

Stored Procedure: Encapsulates complex database logic for secure and efficient transactions.

From Account:

To Account:

Amount ($):

📝 Stored Procedure Code

CREATE PROCEDURE TransferMoney(
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(10,2)
)
AS
BEGIN
    BEGIN TRANSACTION
    
    DECLARE @FromBalance DECIMAL(10,2)
    SELECT @FromBalance = Balance 
    FROM Accounts 
    WHERE AccountID = @FromAccount
    
    IF @FromBalance >= @Amount
    BEGIN
        UPDATE Accounts 
        SET Balance = Balance - @Amount 
        WHERE AccountID = @FromAccount
        
        UPDATE Accounts 
        SET Balance = Balance + @Amount 
        WHERE AccountID = @ToAccount
        
        INSERT INTO Transactions VALUES(
            @FromAccount, @ToAccount, 
            @Amount, 'Success', GETDATE()
        )
        
        COMMIT TRANSACTION
        RETURN 1 -- Success
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION
        RETURN 0 -- Failed
    END
END
                    

Click Execute Transfer to run the procedure.

🎯 Calculate Bonus

Function: Reusable logic to compute values based on inputs, like employee bonuses.

Select Employee:

Performance Score (0-100):

📝 Function Code

CREATE FUNCTION CalculateBonus(
    @Salary DECIMAL(10,2), 
    @Performance INT
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Bonus DECIMAL(10,2)
    
    IF @Performance >= 90
        SET @Bonus = @Salary * 0.20      -- 20%
    ELSE IF @Performance >= 80
        SET @Bonus = @Salary * 0.15      -- 15%
    ELSE IF @Performance >= 70
        SET @Bonus = @Salary * 0.10      -- 10%
    ELSE IF @Performance >= 60
        SET @Bonus = @Salary * 0.05      -- 5%
    ELSE
        SET @Bonus = 0                   -- No bonus
    
    RETURN @Bonus
END

-- Usage Example:
SELECT Name, Salary, 
       dbo.CalculateBonus(Salary, Performance) as Bonus
FROM Employees
                    

Click Calculate Bonus to run the function.

📊 All Employees Bonus Report

🎯 Benefits of Stored Procedures and Functions

Aspect	Regular Query	Stored Procedure	Function
Performance	Slower due to parsing	Faster (pre-compiled)	Fast for reusable logic
Security	Vulnerable to SQL injection	Secure with parameterized inputs	Secure for calculations
Reusability	Repeated code	Reusable across applications	Reusable for computations
Maintenance	Hard to update	Centralized logic	Centralized calculations

🧪 Performance Comparison

Regular Query

2.5s

Average Execution Time

Slow

Function

1.8s

Average Execution Time

Medium

Stored Procedure

1.2s

Average Execution Time

Fast

Click Run Performance Test to see results.

💡 Pro Tips for Stored Procedures and Functions

🔒 Use Parameters: Prevent SQL injection with parameterized queries.
🚀 Optimize Performance: Pre-compile logic with stored procedures.
🔄 Reuse Logic: Centralize business rules in procedures and functions.
🛠️ Modularize Code: Break complex logic into smaller functions.

Pro Tip: "Make your database work smarter, not harder!" 😄