Stored Procedures and Functions Explained

Key Concepts

1. Stored Procedures
2. Functions
3. Difference Between Stored Procedures and Functions
4. Creating Stored Procedures
5. Creating Functions
6. Parameters in Stored Procedures
7. Parameters in Functions
8. Executing Stored Procedures
9. Executing Functions

1. Stored Procedures

A stored procedure is a precompiled collection of SQL statements stored in the database. It can accept input parameters, perform operations, and return results. Stored procedures are used to encapsulate business logic and improve performance by reducing network traffic.

2. Functions

A function is a database object that performs a specific task and returns a single value. Functions can be used in SQL queries and can accept parameters. They are typically used for calculations or transformations.

3. Difference Between Stored Procedures and Functions

The main differences between stored procedures and functions are:

• Return Value: Stored procedures can return multiple values or no value, while functions must return a single value.
• Usage: Functions can be used in SQL queries, whereas stored procedures cannot.
• Execution: Stored procedures are executed using the EXECUTE statement, while functions are called within SQL queries.

4. Creating Stored Procedures

Stored procedures are created using the CREATE PROCEDURE statement.

Example:

CREATE PROCEDURE GetEmployeeDetails
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
END;

5. Creating Functions

Functions are created using the CREATE FUNCTION statement.

Example:

CREATE FUNCTION CalculateSalaryIncrease(@Salary DECIMAL(10, 2), @IncreasePercentage DECIMAL(5, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
    RETURN @Salary + (@Salary * @IncreasePercentage / 100)
END;

6. Parameters in Stored Procedures

Stored procedures can accept input and output parameters.

Example:

CREATE PROCEDURE GetEmployeeByID(@EmployeeID INT)
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
    WHERE EmployeeID = @EmployeeID
END;

7. Parameters in Functions

Functions can accept input parameters but cannot have output parameters.

Example:

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

8. Executing Stored Procedures

Stored procedures are executed using the EXECUTE or EXEC statement.

Example:

EXEC GetEmployeeDetails;
EXEC GetEmployeeByID @EmployeeID = 123;

9. Executing Functions

Functions are executed by calling them within SQL queries.

Example:

SELECT EmployeeID, FirstName, LastName, CalculateSalaryIncrease(Salary, 5) AS NewSalary
FROM Employees;

Understanding stored procedures and functions is essential for efficient database management. By encapsulating logic and performing calculations, these database objects help in maintaining clean and optimized SQL code.