What is Functions and Difference between Functions and Stored Procedures in SQL Server

What is a Function in sql:

• A function is a set of SQL Statements that perform a specific task.
• Basically, it is a set of SQL statements that accept only input parameters, perform action and return the result. Function can return an only single value or a table.
• We can't use a function to insert, update, Delete records in the database tables.
• If you have to repeatedly write large SQL script to perform the same task, you can create a function that performs that task.
• Functions can be call within SQL Statements but Store Procedure cannot be.
• You cannot call stored procedures from within a function.

Types of User-Defined Functions

1. Scalar Functions: Return a single value (e.g., an integer, a string, etc.).
2. Table-Valued Functions (TVFs): Return a table. These can be either inline or multi-statement.

Scalar Functions

Scalar functions take parameters, perform an operation, and return a single value. Here’s how to create and use a scalar function:

CREATE FUNCTION dbo.GetFullName
(
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50)
)
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;

Table-Valued Functions:

Table-Valued User-Defined Functions (TVFs) in SQL Server are a powerful feature that allows you to encapsulate and reuse logic that returns a table.

Types of Table-Valued Functions

There are two main types of TVFs in SQL Server:

1. Inline Table-Valued Functions: These functions are similar to views and are defined with a single SELECT statement.
2. Multi-Statement Table-Valued Functions: These functions can contain multiple statements to build the final table result. They allow for more complex logic compared to inline functions.

Inline Table-Valued Functions

CREATE FUNCTION dbo.GetEmployeesByDepartment
(
    @DepartmentID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, DepartmentID
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);

Multi-Statement Table-Valued Functions

A multi-statement table-valued function can contain multiple statements to build the final table. Here’s how you create one:

CREATE FUNCTION dbo.GetEmployeesByDepartmentMulti
(
    @DepartmentID INT
)
RETURNS @EmployeeTable TABLE
(
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    DepartmentID INT
)
AS
BEGIN
    INSERT INTO @EmployeeTable
    SELECT EmployeeID, FirstName, LastName, DepartmentID
    FROM Employees
    WHERE DepartmentID = @DepartmentID;

    RETURN;
END;

Difference between Function and SP:

1. Purpose

• Stored Procedures: Used to perform a series of tasks (Like Mode 1-Get,2-Insert, 3-Update etc.).
• Functions: A function is a set of SQL Statements that perform a specific task.

2. Return Type

• Stored Procedures: Do not return a value directly. They can return multiple result sets and output parameters.
• Functions: Always return a value (single value or a table).

3. Usage

• Stored Procedures: Called with EXEC or EXECUTE.
• Functions: Called within SQL statements like SELECT, WHERE, or FROM.

4. Parameters

• Stored Procedures: Can take input and output parameters.
• Functions: Only take input parameters.

5. Side Effects

• Stored Procedures: Can change data in the database (e.g., INSERT, UPDATE, DELETE).
• Functions: Should not change data; they just return results.

6. Error Handling

• Stored Procedures: Can handle errors using TRY...CATCH.
• Functions: Cannot handle errors with TRY...CATCH.

7. Performance

• Stored Procedures: Often faster for complex tasks.
• Functions: Can be fast for simple tasks but may be slower for complex tasks.

8. Permissions

• Stored Procedures: Permissions can be managed separately from the tables they access.
• Functions: Need permissions on the function and the tables they access.

9. Maintenance and Versioning

• Stored Procedures: Easier to manage for complex tasks.
• Functions: Good for reusable, simple logic.

In short, use stored procedures for complex tasks and changing data, and use functions for returning values without changing data.