Beyond Queries: Advanced SQL with Stored Procedures & Functions

Unlock the full power of your database with these essential tools.

Welcome! While you can do a lot with simple `SELECT`, `INSERT`, and `UPDATE` statements, the real power of a database is unleashed with **Stored Procedures** and **Functions**. These are pre-compiled SQL code blocks that are stored in the database. They help you write more efficient, secure, and reusable code. Although they sound similar, they have key differences that determine when you should use each one.

Stored Procedures

A Stored Procedure is a pre-written piece of code that you can execute to perform a task. It's best used for tasks that involve data manipulation and a sequence of steps. Think of it as a **script or a routine** for your database.

Key Characteristics:

• Purpose: To perform a task or a series of operations (e.g., updating a customer's record, generating a report, or inserting new data).
• Return Value: Can return multiple values or no values at all. It cannot be used directly in a `SELECT` statement.
• Usage: Executed using the `EXEC` or `EXECUTE` command.
• Security: Procedures are a great security tool, as you can grant a user permission to execute a procedure without giving them direct access to the underlying tables.

Example: A procedure to add a new employee and log the action.

CREATE PROCEDURE AddEmployee @Name VARCHAR(50), @Department VARCHAR(50)
AS
BEGIN
   INSERT INTO Employees (Name, Department) VALUES (@Name, @Department);
   INSERT INTO Logs (Action) VALUES ('New employee added');
END;
            

You would then run this procedure with: `EXEC AddEmployee 'Jane Doe', 'Marketing';`

---

Functions

A Function is a pre-written piece of code that takes a set of parameters and returns a single value. It's best used for tasks that involve calculation or data retrieval. Think of it as a **formula** for your database.

Key Characteristics:

• Purpose: To compute and return a single value.
• Return Value: Must return a single value and can be used directly within a `SELECT` statement.
• Usage: Called like a standard formula, for example, `dbo.FunctionName()`.
• Types: Can be scalar (returns a single value) or table-valued (returns a table).

Example: A function to calculate the total number of days an employee has worked.

CREATE FUNCTION GetWorkingDays (@HireDate DATE)
RETURNS INT
AS
BEGIN
    DECLARE @Days INT;
    SELECT @Days = DATEDIFF(day, @HireDate, GETDATE());
    RETURN @Days;
END;
            

You would then use this function in a query like: `SELECT Name, dbo.GetWorkingDays(HireDate) AS WorkingDays FROM Employees;`

Understanding the difference between these two powerful tools is key to writing efficient and maintainable database code.

Master SQL with more of our coding tutorials!