Functions in SQL Server

Function is a group of T-SQL statements used for defining reusable logic.Unlike stored procedure function always returns some value.The return value can be of different data types such as a table or a single scalar value.

Also the only way to return a value from a function is through the return value as functions don’t have out parameters as is in the case of stored procedures.

We can broadly classify functions depending upon the values they operate upon and the value which is returned

Rowset functions Returns resultset that can be used in place of a table in a select statement.
Aggregate functions Operates on a set of values and returns a single value.
Scalar functions Operates on a single value and returns a single value.

Depending whether they are inbuilt or not functions can be classified as:

Inbuilt Functions These are provided by SQL Server.
User Defined Functions Depending on the requirement we can implement user defined functions.

Some of the examples of inbuilt functions are:

Aggregate functions

These performs an operation on a set of values and returns a single value

Function	Use
AVG	Returns the Average value in group
MIN	Returns the minimum value in group
MAX	Returns the maximum value in group
SUM	Returns the total of items in group
COUNT	Returns the numbers of items in group

Following StudentsInfo table stores the Details about the students such as their marks.

We can use different aggregate functions to get aggregated data about the Students in the StudentsInfo table.

SELECT SUM(MARKS) as TOTAL,AVG(MARKS) AVERAGE,MAX(MARKS) HIGHEST ,MIN(MARKS) LEAST,COUNT(MARKS) [NUMBER OF STUDENTS]
FROM dbo.StudentsInfo

Scalar function

Operates on a single value and returns a single value

Function	Use
CAST	Converts expression of one type to another data type.
CONVERT	Converts expression of one type to another data type. This is specific to SQL Server
LOWER	Converts a string value to lowercase
UPPER	Converts a string value to uppercase
LTRIM	Removes spaces from the beginning of a string
RTRIM	Removes spaces from the end of a string

Rowset functions

Returns a resultset that can be used in a select statement.

Function	Use
OPENQUERY	Performs Query on a Linked Server
OPENXML	Provides rowset from a XML document

User Defined Functions

When creating a user defined function we first identify the type of value that will be returned by the function:

Scalar Function Function which returns a scalar value.
Table Valued Function Function which returns a resultset.

We can create functions in SQL Server using the Create Function statement.Following is the syntax for creating a user defined function.

CREATE FUNCTION FuntionName
( Parameter as Datatype)
Returns Type
As

Function Body

We will create a scalar function and a table valued function for querying the StudentsInfo table.StudentsInfo table contains data about the students.

Scalar Function

We create the FnGetStudentsSummary scalar function which will return the details about the number of the countries to which students belong .

ALTER FUNCTION dbo.FnGetStudentsSummary
(
@country varchar(50)=''
)
RETURNS int
AS
BEGIN
DECLARE @COUNT INT
IF @country<>''
SELECT @COUNT=COUNT(*) FROM [dbo].[StudentsInfo]
WHERE [COUNTRY]=@country
ELSE
SELECT @COUNT=COUNT(distinct([COUNTRY])) FROM [dbo].[StudentsInfo]

RETURN (@COUNT)
END
GO

The function FnGetStudentsSummary will return the country details of the users.It expects one input parameter.@country is a default parameter.
If we do not supply any value for this parameter then we will get the distinct countries in the table.For example if we execute the following query:

SELECT dbo.FnGetStudentsSummary('India') AS 'TOTAL COUNTRY'

then we will get the result as 2 as the number of students from India are 2 .

If we want the number of different countries to which Students belong then we can use the following query.To use the functions default parameter value we specify the word DEFAULT.

SELECT dbo.FnGetStudentsSummary(DEFAULT) AS 'TOTAL COUNTRY'

This will display the value 3 as there 3 different countries in the StudentsInfo table.

To get the different countries we use the default parameter value.

TABLE VALUED FUNCTION

Table valued functions can return a result set.The value returned by a table valued function can be directly used in a select statement.

To get the data for a specific user we will create a table valued function.The name of a user consists of two columns.In the function we are concatenating the values of the FIRSTNAME and LASTNAME columns.

Create FUNCTION dbo.FnGetStudentDetailById(@id int)
RETURNS TABLE
AS
RETURN

SELECT UPPER(FIRSTNAME)+' '+UPPER(LASTNAME) AS NAME,UPPER(COUNTRY) AS COUNTRY FROM StudentsInfo WHERE ID=@id

We can use the FnGetStudentDetailById() function in a select statement similarly to how we use a table:

SELECT * FROM FnGetStudentDetailById(1)

This will display the name and country of the student with Id “1”:

Name Country

AMIT KUMAR INDIA

ashish

Follow on: