SQL Functions | SQL Tutorial and Query Example

Text copied!

SQL Functions

May 29, 2023, 11:11 a.m. under SQL
    • Types of SQL functions :

    1. User-defined function

    1. User-defined function :

    A user-defined function is a custom function created by a user. SQL Server allows you to create your own functions using the 'CREATE FUNCTION' statement that enables you to perform specific calculations.

    2. System-defined function :

    SQL Server provides extensive collection of built-in system functions is available that perform various tasks related to the database system. It is also famous as 'built-in functions'.

    Here's the syntax :
    SQL Functions
    Input Parameter :

    In SQL Server, function accepts many or no parameter to execute specific calculation and returns a single result. A parameter is a value that is passed into a function, on which a specific task is performed.

    Return type :

    In SQL Server, every function must return a value. This means that if the output of the function is a number, you should define the return type as INT.

    Calling function :

    Calling function simply means executing or running it. To call a function in SQL Server, you can use the 'SELECT' statement in a SQL query.

    CREATE FUNCTION function_name
(
    @parameter1 data_type,
    @parameter2 data_type,
    ...
)
RETURNS output_data_type
AS
BEGIN

   -- Write SQL statements here to perform desired operation

    RETURN output_value;
END;
    Here's an example of SQL user-defined function :

    [i] Let's assume you want to create a function that adds two numbers.

    To enhance your understanding of 'SQL functions', be sure to check out our next tutorial.
    SELECT schema_name.function_name( arguments );
        
    CREATE FUNCTION AddtwoNumbers
(
    @num1 INT,
    @num2 INT
)
RETURNS INT
AS
BEGIN
    RETURN @num1 + @num2;
END;
    SQL Functions 
    SELECT dbo.AddtwoNumbers(1, 4);
    SQL Functions

    Frequently Asked Questions :

    Functions of SQL include data retrieval, manipulation, insertion, deletion, and management of databases.
    Five built-in functions in SQL are COUNT, SUM, AVG, MAX, and MIN.
    The two primary functions of SQL are data manipulation language (DML) and data definition language (DDL).
    SQL method refers to the process or technique used to execute SQL commands or queries to interact with databases.
    « Previous
    Next »
    RELATED :