Stored Procedure and Function ka Funda

Understand how Stored Procedure and Function works with Fullstackgada

👥 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

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
                    

procedure run karne ke liye Execute Transfer par click kare..

🎯 Calculate Bonus

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
                    

Function ko run karne ke liye Calculate Bonus par click kare..

📊 All Employees Bonus Report

Regular Query

2.5s

Average Execution Time

Slow

Function

1.8s

Average Execution Time

Medium

Stored Procedure

1.2s

Average Execution Time

Fast

🧪 Performance Test

Run karne ke liye Run Performance Test par Click kare

Performance test ka results yaha show hoga...