FUNCTIONS in SQL Server

A function is a set of codes that returns a value, the value can be Scalar or in the form of a table.

There are two types of functions in SQL Server. “Built-in” Functions and “User Defined” Functions or UDFs.

Some common example of build in functions are @@SERVERNAME , DAY() , MONTH() , CAST() , GETDATE() , SUM() , ROUND() , EVENTDATA() , SQRT() , YEAR() , ABS() , EVENTDATA() , LOWER() , @@CURSOR_ROWS , CONVERT() , FLOOR() , CEILING() , UPPER() .

There are THREE types of USER-DEFINED functions in SQL Server

Scaler Functions
Multi-Statement Table Functions
Inline Table-Valued Functions

Scaler Functions

A SCALAR Function is a Type of FUNCTION that will take the input value and Always return a single data value.

In the below example, we have created a function for the addition of two numbers note that we are using a BEGIN and an END Statement to write the code, While in an INLINE TABLE VALUED, we don’t use a BEGIN and END Statement.

Also, note that we can call a Scalar Function within a select statement, unlike a Table-valued function which is called a table.

We can also use a Scalar Function in a WHERE Clause to Filter out the data.

a scalar function should return a value and there should be a return statement “RETURNS” and the Return datatype

CREATE OR ALTER FUNCTION dbo.addition (@x INT,@y INT)
RETURNS INT
BEGIN
RETURN @X+@Y
END;
go
select dbo.addition (2,2) SUM

Multi-Statement Table Functions

Like a SCALAR Function, a table-valued function also returns a value but as a table, Below is an example of an ADDITION function that has the same output as a scalar function but this time it returns a table.

a Multi-Statement Table Function also has a BEGIN and END statement, Unlike Inline Table Valued Function which doesn’t use a BEGIN and an END statement.

Please note that We need to DECLARE a Table and its Column with their datatypes which will be returned from the function.

The BEGIN and END statements must populate and return the Declared table.

Since this is a Table function it can be used as a normal table and it can also be used to join to other tables.

CREATE OR ALTER FUNCTION dbo.addition1
(@x INT,@y INT)
RETURNS @SUM TABLE (SUM1 INT)
BEGIN
INSERT INTO @SUM (SUM1)
SELECT @X+@Y
RETURN
END;
go--USE THE TABLE VALUED FUNCTION AS A TABLE
select * FROM DBO.addition1 (2,2);

Inline Table-Valued Functions

The inline Table-valued Function will also take an Input Parameter and will return a Table.

Below is another example of an Inline Table-Valued Function, Please note that there are no BEGIN and END Statements, There is a Return Type “TABLE” and note that there is one SQL Statement that returns a Table, as the result of running an Inline Table Valued function is a Table it can be used as a table and can also be used to join to other tables.

CREATE OR ALTER FUNCTION dbo.addition3 
(@x INT,@y INT)
RETURNS TABLE 
AS RETURN(SELECT @X+@Y AS SUM1)
go
select * FROM dbo.addition3 (2,2)

Some Important Points on Functions

Functions can be used in SELECT statements
They can be used in constraint definition on a table
Functions can be used in computed columns
Functions can be used as a table.
A TABLE Function can also be used to join to a table as we can do with a normal table.
Functions can be used within a store procedure, However, a function cannot be used to call a store procedure.
Functions can call other Functions.
You can nest Functions within Functions and the max level of Nesting is 32.
We can use CTE or Common Table Expressions inside a UDF
A UDF cannot have an OUTPUT Clause.
A Store Procedure can return Multiple Results Sets, but a FUNCTION can only return a single result set.
A UDF doesn’t support “TRY CATCH”.
A UDF doesn’t Support Temp Tables.

FUNCTIONS in SQL Server