User defined functions in T-SQL

User defined functions are set of codes which returns some value may be in terms of scalar value or table value after performing operations. These codes are reusable and made to perform any operation. It can have multiple input parameters but only one return.

Types of UDF in T-SQL

1. Scalar function:
1. deterministic
2. non-deterministic
2. Inline function:
3. Table valued function:

Scalar Functions: In this type of function it returns only one value of any data type except table. For integer data type we have two types:

1. deterministic: It means output is totally dependent on input
2. non-deterministic: That means output is no dependent on input

           CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS <data-type>
               AS
               BEGIN
                      <statements>
                      Return <variable>
               END

Inline Function: This type of function only returns table type with only single statement. This function does not have BEGIN...END.

       CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS TABLE
               AS
               RETURN (
                      <statements>
               )

Table Valued Function:  This type of function also returns table type but by creating table variable in definition with data type for each column.
              
       CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS <table-variable> TABLE (<columns with data-type>)
               AS
               BEGIN
                      <statements>
                      RETURN
              END

Pros: Precompiled which makes execution faster and reusable. A function can call another function and stored procedure also can call function. Can return table type.

Cons: We can not execute stored procedure inside function. Can not have output parameter, So can return only one value of any data type.