Function is a database object in
Sql Server. User define functions are routines, method or protocol which
performs calculations, actions such as complex calculations (find the age or
some mathematical calculation) and return the result of that action. Function
may or may not have parameters, but always either a scalar value or a result
set. The return value can be any data type except text, Image, ntext, cursor
and timestamp.
There are three Types of User Define
Function UDFS in Sql Server:
1. Scalar
2. Inline Table-Valued
3. Multi-statement Table-Valued
2. Inline Table-Valued
3. Multi-statement Table-Valued
Scalar function
A Scalar UDF can accept zero or many
input parameter and will return a single value. A Scalar user-defined
function returns one of the scalar (int, char, varchar etc.) data types. Text, ntext, image and timestamp data types
are not supported.
Let’s start step by
step to create User Define Functions.
Here I am creating a simple function. In this function I am
passing date and returning the year of passing date.
CREATE FUNCTION dbo.fnGet_Year(@Date AS DateTime)
RETURNS INT
AS
BEGIN
RETURN YEAR(@Date)
END
|
Function created successfully.
We can call this function using select statement.
Inline Table-Valued
An
inline table-valued function returns a variable of data type table whose value
is derived from a single SELECT statement. Since the return value is
derived from the SELECT statement, there is no BEGIN/END block needed in the
CREATE FUNCTION statement. There is also no need to specify the table variable
name (or column definitions for the table variable) because the structure of
the returned value is generated from the columns that compose the SELECT
statement. Because the results are a function of the columns referenced in
the SELECT, no duplicate column names are allowed and all derived columns must
have an associated alias.
It return
a table data type. Inline functions can be used to achieve the
functionality of parameterized views.
Example
Here I
am creating Inline Table – valued function
CREATE FUNCTION dbo.fn_Get_EmpNames_By_Area
( @eadd nvarchar(50) )
RETURNS table
AS
RETURN (
SELECT
* from EMP
WHERE
empAdd = @eadd
);
|
Function
created successfully.
Now I
am calling this function
It
returns the list of employee which area is pune.
Multi-statement Table-Valued
A
Multi-Statement Table-Valued user-defined function returns a table. It can have
one or more than one T-Sql statement. Within the create function command
you must define the table structure that is being returned.
See the
example
CREATE FUNCTION [dbo].[Get_Gender_Wise_Records]
(
@Gender
varchar(10)
)
RETURNS @Gender_Wise_Records
TABLE
(
EmpID varchar(50) not null,
JobTitle varchar(50),
MaritalStatus
varchar(10)
)
AS
BEGIN
IF (@Gender = 'M')
BEGIN
INSERT
INTO @Gender_Wise_Records
SELECT
BusinessEntityID,JobTitle,MaritalStatus
FROM
Employee
WHERE
Gender='M'
END
ELSE
BEGIN
INSERT
INTO @Gender_Wise_Records
SELECT
BusinessEntityID,JobTitle,MaritalStatus
FROM
Employee
WHERE
Gender='F'
END
RETURN
;
END
|
Function
is created successfully.
Now I
am executing this function.
Benefits of User Define Function
1.
Reusable
2.
Faster
3.
Easy to maintain
4.
Reduce network traffic
Drawback of User
Define function
1.
Function is not used to Insert, Update, Delete
data in database.
2.
Can’t modify the state of the database.
3.
UDF does not support Try-catch, @ERROR or
RAISERROR function.
4.
Function can be nested up to 32 levels.
5.
UDF can’t return XML data type
6.
UDF can have up to 1023 input parameters.
7.
Does not support Row Count.
8.
Doesn't support Exception handling.
