User defined
Function:
User
defined Function in SQL server is a set of statements or block of statements. SQL Server user-defined functions are routines that
accept parameters, perform an action and return the result of that action as a
value. UDF can be
used to perform complex calculations.
Function can return two types or output i.e. Single value (scalar) or Table. We can’t modify data using functions, i.e. insert, update and delete.
Function can return two types or output i.e. Single value (scalar) or Table. We can’t modify data using functions, i.e. insert, update and delete.
Two
types of user defined functions are there in SQL server:
1. Scalar
valued user defined Functions
2. Table value
User defined Functions
1. Scalar Valued UDF: User defined scalar function
returns single value as a result of function. We can return any data-type value
from function.
Example:
Create Table:
Create Scalar Valued Function:
(
@age int
)
returns int
As
Begin return (Select COUNT(*) from
Student_info where Age=@age);
End
Execute Function:
select dbo.fnGetStudent_Info(25) as 'TotalNo'
OutPut:
 |
2. Table Valued UDF:
User defined
Table Valued functions returns table variable as a result. There are two types of
table value functions:
1. Inline Table Valued: Returns table variable as a result.
Example
Create Function:
Create function fnGetStudent()
returns Table
As
return (Select * from
Student_info )
Execute Function:
select * from
fnGetStudent()
Output:
 |
2. Multi- statement Table valued UDF:
It returns table variable as result of action
performed by function. Table must be declared and defined explicitly. Value of
table variable will be derived from multiple statements.
Example:
Create Function:
Create function fnGetMULStudent()
returns @Student Table
(
Student_Id int,
Name varchar(100),
class varchar(100)
)
As
Begin
insert into @Student select
s.Student_id,
s.Student_name,S.class from
Student_info s
update @Student set class='MPhill' where
Student_id=6
return
end
Execute Function:
select * from
fnGetMULStudent()
Output:
 |
Limitations
or Restriction in Functions:
1.
Function cannot be used to perform action that can modify data ex.
Insert, Update & Delete.
2.
User-defined
functions cannot return multiple result sets. Functions return single value.
3.
Function does not
support Error handling. TRY…CATCH, @ERROR or RAISERROR are restricted.
4.
Functions cannot
call a stored procedure, but can call an extended stored procedure.
5.
We can’t use dynamic
SQL or temp tables in functions. Table variables are allowed.
6.
SET statements are
not allowed in a user-defined function. Ex set ROWCOUNT etc.
7.
User Defined
Function can't returns XML Data Type.
8.
User-defined
functions can be nested; that is, one user-defined function can call another.
Function can be nested up to 32 levels.
9.
Function accepts
only input parameters (unlike stored procedure).
