Procedures and Functions

🧙‍♂️ SQL Procedures & Functions: Your Database’s Secret Helpers

Imagine this: You have a magic recipe book. Instead of cooking the same dish from scratch every time, you write down the recipe once. Then you just say the recipe name, and poof — the dish appears!

That’s exactly what stored procedures and functions are in SQL. They’re your saved recipes that the database remembers forever.

🏠 The Kitchen Analogy

Think of your database as a big kitchen:

Once you write a recipe (procedure/function), you can use it again and again without rewriting the steps!

📜 Stored Procedure Basics

What is a Stored Procedure?

A stored procedure is a saved set of SQL commands. It lives inside your database and waits until you call it.

Real Life Example:

• Your mom has a recipe for chocolate cake
• She wrote it down once
• Now anyone can make that exact cake by following her recipe

Why Use Stored Procedures?

• ✅ Saves Time — Write once, use forever
• ✅ Fewer Mistakes — Same correct steps every time
• ✅ Organized — Keep your code neat and tidy
• ✅ Faster — Database already knows the plan

🔨 CREATE PROCEDURE

This is how you write your recipe card!

The Basic Recipe

CREATE PROCEDURE SayHello
AS
BEGIN
    SELECT 'Hello, World!';
END;

Breaking it down like a 5-year-old:

1. CREATE PROCEDURE — “I want to make a new recipe”
2. SayHello — “Let’s call it SayHello”
3. AS BEGIN — “Here’s what to do…”
4. SELECT 'Hello, World!' — “Show this message”
5. END — “That’s all!”

A Real Example

Let’s make a procedure that shows all customers:

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
    SELECT * FROM Customers;
END;

Now the database remembers this forever! 🎉

📦 Procedure Parameters

What are Parameters?

Parameters are like ingredients you give to your recipe.

Example: A pizza recipe needs to know:

• What size? (small, medium, large)
• What toppings? (cheese, pepperoni)

In SQL, parameters tell the procedure what specific information to use.

Input Parameters (Giving Ingredients)

CREATE PROCEDURE GetCustomerByCity
    @CityName VARCHAR(50)
AS
BEGIN
    SELECT * FROM Customers
    WHERE City = @CityName;
END;

What’s happening:

• @CityName is our ingredient (parameter)
• The procedure uses it to find customers in that city

Output Parameters (Getting Results Back)

CREATE PROCEDURE CountCustomers
    @TotalCount INT OUTPUT
AS
BEGIN
    SELECT @TotalCount = COUNT(*)
    FROM Customers;
END;

Think of it like: “Hey recipe, after you’re done, tell me how many cookies you made!”

Multiple Parameters

CREATE PROCEDURE FindOrders
    @CustomerID INT,
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SELECT * FROM Orders
    WHERE CustomerID = @CustomerID
    AND OrderDate BETWEEN @StartDate
                      AND @EndDate;
END;

You can have as many ingredients as you need!

📞 Calling Stored Procedures

How to Use Your Recipe

Once you create a procedure, you need to call it (like asking mom to make the cake).

Simple Call (No Ingredients Needed)

EXEC SayHello;

Or:

EXECUTE SayHello;

Both work the same way!

Call with Parameters

EXEC GetCustomerByCity 'New York';

Or more clearly:

EXEC GetCustomerByCity @CityName = 'New York';

Call with Output Parameter

DECLARE @MyCount INT;
EXEC CountCustomers @TotalCount = @MyCount OUTPUT;
SELECT @MyCount AS TotalCustomers;

Like saying: “Make the recipe and put the answer in this box for me!”

graph TD
    A["👤 You"] -->|EXEC GetCustomerByCity 'NYC'| B["📜 Procedure"]
    B --> C["🔍 Database searches"]
    C --> D["📊 Results come back"]

⚡ User-Defined Functions

What’s Different About Functions?

A function is like a calculator button. You give it a number, it calculates, and gives you back ONE answer.

Creating a Simple Function

CREATE FUNCTION AddTax(@Price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Price * 1.10;
END;

What this does:

• Takes a price
• Adds 10% tax
• Gives back the new price

Using a Function

SELECT dbo.AddTax(100.00) AS PriceWithTax;

Result: 110.00

Functions fit right inside your queries! That’s their superpower! 🦸

🔢 Scalar Functions

What is “Scalar”?

Scalar means “single value.” A scalar function returns just ONE thing — like a number, text, or date.

Example: Age Calculator

CREATE FUNCTION CalculateAge
    (@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate,
                    GETDATE());
END;

Using Scalar Functions in Queries

The magic is using them inside your SELECT:

SELECT
    CustomerName,
    BirthDate,
    dbo.CalculateAge(BirthDate) AS Age
FROM Customers;

Every row gets its own calculated age!

More Scalar Function Examples

Formatting Names:

CREATE FUNCTION FormatName
    (@FirstName VARCHAR(50),
     @LastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @LastName + ', ' + @FirstName;
END;

Usage:

SELECT dbo.FormatName('John', 'Smith');
-- Result: 'Smith, John'

⚖️ Functions vs Procedures

The Big Comparison

graph TD
    A["Need to do something?"] --> B{Return a single value?}
    B -->|Yes| C["✨ Use a FUNCTION"]
    B -->|No| D{Multiple operations?}
    D -->|Yes| E["📜 Use a PROCEDURE"]
    D -->|No| F["Either works!"]

Side-by-Side Comparison

When to Use Each

Use a PROCEDURE when:

• 📝 You need to INSERT, UPDATE, or DELETE data
• 🔄 You’re doing many different operations
• 📊 You don’t need to use the result in a SELECT

Use a FUNCTION when:

• 🧮 You need to calculate something
• 📋 You want to use it in a SELECT query
• 🔢 You need ONE answer back

Real World Examples

PROCEDURE: Processing an order

CREATE PROCEDURE ProcessOrder
    @OrderID INT
AS
BEGIN
    UPDATE Orders
    SET Status = 'Shipped'
    WHERE OrderID = @OrderID;

    INSERT INTO OrderLog (OrderID, Action)
    VALUES (@OrderID, 'Shipped');
END;

FUNCTION: Calculating discount

CREATE FUNCTION GetDiscount
    (@CustomerType VARCHAR(20))
RETURNS DECIMAL(5,2)
AS
BEGIN
    RETURN CASE @CustomerType
        WHEN 'Gold' THEN 0.20
        WHEN 'Silver' THEN 0.10
        ELSE 0.00
    END;
END;

Using the function in a query:

SELECT
    ProductName,
    Price,
    Price * (1 - dbo.GetDiscount('Gold'))
        AS FinalPrice
FROM Products;

🎯 Quick Summary

🚀 You Did It!

Now you know:

• ✅ How to create stored procedures
• ✅ How to pass parameters in and out
• ✅ How to call procedures
• ✅ How to create and use functions
• ✅ When to choose functions vs procedures

Remember: Procedures are your recipe cards. Functions are your calculator buttons. Both save you time and make your database smarter!

🧙‍♂️ You’re now a database wizard who can create magical helpers!