What are Functions in SQL SERVER?

Functions are the objects which performs an action based on input values (input parameters) and can return only single object (can be a single value or single table) unlike stored procedure. It is basically a set of sql statements.

SQL SERVER Provides many system defined / built-in functions and user defined functions (UDF). Below diagram shows the classification of functions .

Built-in Functions in SQL

They are the system defiend functions, which cannot be modified. They are listed below:

• Scalar Functions
• Rowset Functions
• Aggregate Functions
• Ranking Functions

Scalar Functions

They are the functions which operate on single value and also return a single value.

Examples :
Syntax : SQUARE(float_expression)
select SQUARE(10) AS Result

Syntax : REVERSE (string_expression)
select REVERSE('Seek Your Career')

Aggregate Functions

They are the functions that works on a collection of values but return a single value.

Examples:
Syntax : SUM(ALL | DISTINCT expression)
Note : Default is ALL
SAMPLE TABLE

Other functions are : MIN(), MAX(), COUNT() etc.

User Defined Functions

Scalar Functions

They are the UDF (User defined functions) which returns a single value. The data type of the value returned is defined with the RETURNS Clause.

TIP: The return type can be any data type except text, ntext, image, cursor, and timestamp.

Example:
Table Used : Personal_Detail

Now let us create a scalar function which take house_no, city and state as input and give us one string value as address combining these 3 column values.
CREATE Function fun_add
(@House_No varchar(20),
@city varchar(20),
@state varchar(20)
)
RETURNS varchar(40)
AS
BEGIN
RETURN (select @House_No +', '+ @city+', '+ @state)
END

Execute above statements to create Function – fun_add().

How to call a Function?

Select id, name, dbo.fun_add(House_No, city, state) as Address from personal_detail

OUTPUT :

You can see that a custom column is created according to the value returned from the function.

Table Valued Functions

This user defined Table Valued Function returns a table. In it we can have inline table-valued and multistatement table-valued functions.

Inline Table-Valued Functions :

Here it does not have the function body, the returned table is the result set of the SELECT statement.

Example:
Table used in this example – Personal_Detail

Let us create a function which takes state as input and gives all the matching rows as a table.

CREATE function search_state
(
@state varchar(15)
)
RETURNS TABLE
AS
RETURN (SELECT * from personal_detail where state = @state)

Execution : SELECT * from search_state(‘Maharashtra’)
OUTPUT

Here you can see that the function is returning the result set of SELECT statements.

MultiStatement Table-Valued Functions :

It do contain function body within BEGIN and END block. Contains a set of T-SQL statements that creates and insert values into a table and return that table as an output.

Let us create a function to demonstrate the same

Table Used: Personal_Detail

Create a function which creates a table, insert values into it and do some updation in the newly created table but not modifying the original table.

CREATE function fun_getemptable()
RETURNS @detail TABLE
(
ID int,
Name varchar(20),
State varchar(20)
)
AS
BEGIN
Insert into @detail select id, name, state from Personal_Detail
UPDATE @detail set state = 'Himachal' where state = 'Maharashtra'
return
END

Execution : select * from fun_getemptable()
OUTPUT

Here we have created a detail table inside the function and inserted values from our original personal_detail. Moreover the updation take place only in the detail table and no modification is done in the original table.

NOTE : You cannot use DML statements with functions. Like INSERT, UPDATE AND DELETE.