Skip to main content

Creating Your Own Functions

SQL Functions: Creating Your Own Functions

In SQL, functions play a vital role in performing operations. They assist in manipulating data and performing calculations. Apart from built-in functions provided by SQL, users can create their own functions. In this article, we will cover how to create your own functions in SQL.

What Are User-Defined Functions?​

User-Defined Functions (UDFs) are functions defined by the user that can be used to encapsulate a sequence of operations into a single unit. The primary advantage of UDFs is that they are reusable, meaning they can be used in various parts of the SQL code without being rewritten.

Types of User-Defined Functions​

There are two main types of UDFs:

  1. Scalar Functions: These functions return a single value.
  2. Table-Valued Functions: These functions return tables.

Creating Scalar Functions​

Let's start by creating a simple scalar function. The syntax for creating a scalar function is as follows:

CREATE FUNCTION FunctionName (@parameter1 dataType, @parameter2 dataType,...)
RETURNS returnType
AS
BEGIN
    -- function body
    RETURN @Result
END;

Suppose we want to create a function that adds two numbers:

CREATE FUNCTION AddNumbers (@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT;
    SET @Result = @num1 + @num2;
    RETURN @Result;
END;

To use this function, you would use the following syntax:

SELECT dbo.AddNumbers(5, 10) AS Result;

Creating Table-Valued Functions​

Now, let's create a table-valued function. The syntax for creating a table-valued function is as follows:

CREATE FUNCTION FunctionName (@parameter1 dataType, @parameter2 dataType,...)
RETURNS TABLE
AS
RETURN (
    -- SQL query
);

Suppose we want to create a function that returns a table of employees from a specific department:

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

To use this function, you would use the following syntax:

SELECT * FROM GetEmployeesByDepartment(1);

Modifying and Deleting Functions​

To modify a function, you can use the ALTER FUNCTION statement, and to delete a function, you can use the DROP FUNCTION statement. Here's how you can do it:

-- Alter function
ALTER FUNCTION AddNumbers (@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
    -- New function body here
    RETURN @Result;
END;

-- Drop function
DROP FUNCTION AddNumbers;

Conclusion​

User-defined functions in SQL can be quite handy when dealing with complex operations that need to be performed multiple times. They not only help in reusability but also in making your SQL code cleaner and more readable.

Remember, practice is the key to mastering SQL functions. So, try creating and using different types of functions in your SQL queries. Happy coding!