What is an SQL Server function?

A T-SQL function in SQL Server database represents an user-defined object that contains one or more SQL statements to perform a specific operations. A SQL Server function accepts input parameters, perform operations, and returns the result of that operation as a value.
A SQL Server user-defined function can be easily integrated while developing various applications or designing a database. It has many applications and helps programmers solve complex problems.

What is the purpose of a SQL Server function?

A SQL Server user-defined function(Transact-SQL function) is useful when you want to keep one or more sql statements in the same block, this way the function can be reused.

Types of functions

Scalar functions. User-defined scalar functions return a single data value. The return type can be any data type(most used: numeric, date & time, string) except text, ntext, image, cursor, and timestamp.
Table-valued functions. User-defined table-valued functions return a table data type.

How to create a function with T-SQL?

To create a function in SQL Server with T-SQL uses the following syntax:

CREATE OR ALTER FUNCTION function_name(parameters)
RETURNS data_type AS
BEGIN
SQL_statements
RETURN return_value
END
GO

The function_name is the name of the function in the above syntax.
The input parameters are given in the round brackets. It also has the data types.
Data_type is the data type of the value the function will return.
SQL_statements is the select statement defined by user.
Return_value is the value that the function will return.

Create Function return single value

The example below shows how to create a function in the SQL Server database using the T-SQL language. The created function is of scalar type, the CREATE FUNCTION keyword is used, the function contains an int type parameter and returns a single value, the returned type is money.

CREATE FUNCTION CtrAmount ( @Ctr_Id int(10) )
  RETURNS MONEY
  AS
  BEGIN
      DECLARE @CtrPrice MONEY
        SELECT @CtrPrice = SUM(amount)
          FROM Contracts
        WHERE contract_id = @Ctr_Id
      RETURN(@CtrPrice)
  END
GO

SELECT * FROM CtrAmount(345)
GO

Create Function return Table

The example below shows how to create a Table-valued function in the SQL Server.
The CREATE FUNCTION keyword is used, the function contains an int type parameter and returns a table data type.

CREATE FUNCTION function_name (@PRODUCT_ID Int)
  RETURNS @ProductsList Table
    (Product_Id Int,
     Product_Dsp nvarchar(150),
     Product_Price Money )
AS
  BEGIN
    IF @PRODUCT_ID IS NULL
      BEGIN
        INSERT INTO @ProductsList (Product_Id, Product_Dsp, Product_Price)
        SELECT Product_Id, Product_Dsp, Product_Price
        FROM Products
      END
    ELSE
      BEGIN
        INSERT INTO @ProductsList (Product_Id, Product_Dsp, Product_Price)
        SELECT Product_Id, Product_Dsp, Product_Price
        FROM Products
        WHERE Product_Id = @PRODUCT_ID
      END
    RETURN
  END
GO