How To Create a Function With or Without a Parameter in SQL

In this article, we will learn about the With or Without a Parameter.

User-defined functions can be either with parameter functions or without parameter functions, depending on whether they accept input parameters or not.

Parameterized function :

parameters is known as a user-defined function. A user-defined function can accept input parameters, perform some calculations and return a value. with parameter function accepts input parameters and returns output based on the input provided.

Syntax of Parameterized function

CREATE FUNCTION function_name (parameters)
RETURNS return_type
AS
BEGIN
   -- function body
END

For example,

you could create a user-defined function to calculate the sum of two numbers:

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

Without Parameterized function :

without parameters is known as a scalar function. A scalar function returns a single value based on a calculation or without parameter function does not accept any input and always returns the same output, regardless of the context in which it is called. with parameter function accepts input parameters and returns output based on the input provided.

Syntax of Without Parameterized function

CREATE FUNCTION function_name () 
RETURNS return_type 
AS 
BEGIN 
    -- function body 
END

For example,

you could create a scalar function to return the current date:

-- Example of a function without a parameter
CREATE FUNCTION example_function_without_parameter()
RETURNS INTEGER
BEGIN
    DECLARE @result INTEGER;
    SET @result = 5 + 5;
    RETURN @result;
END;

Both parameterized and non-parameterized functions are used to encapsulate complex logic and provide a simple and reusable way to perform specific tasks. They can be called from within other SQL statements, such as SELECT, UPDATE, and DELETE, to add functionality and simplify the code.

It is important to note that different relational database management systems (RDBMS) may have different implementation of functions and different syntax for calling functions, so it is important to consult the documentation of the specific RDBMS being used for more information.