Functions in
SQL server:
Function
in SQL server is a set of statements or block of statements. Function is a
database object that accepts zero or more parameters and return Output.
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.
Functions
are different from stored procedures. Functions must return a value, but stored
proc may or may not return a value. For
More difference between Stored Proc and Functions, Read the article:
Type of Functions:
A. System
Defined Functions
B. User defined
functions
A. System Defined Functions: These
are the inbuilt functions in SQL server.
There Are two type of system defined function:
1.
Scalar Functions: These functions accept input
parameter and return single value.
Some Useful Scalar Functions In SQL SERVER
|
|
Function
|
Description
|
rand(5)
|
Generate random no of 5
characters
|
upper('test')
|
Convert string to Upper case
i.e. TEST
|
lower(‘TEST’)
|
Convert string to lower case
i.e. test
|
ltrim(' test)
|
Remove spaces from left side of
string
|
abs(-5.6)
|
Return absolute no of given
input i.e. 5.6
|
2. Aggregate Functions: These functions accept collection of values as input and generate single value result.
Some
Useful Aggregate Functions In SQL SERVER
|
|
Function
|
Description
|
avg()
|
Returns
Average value from collection of values
|
Min()
|
Returns
minimum value from collection of
values
|
max()
|
Returns
maximum value from collection of values
|
count()
|
Returns
count of collection of values
|
B. User defined Function:
User defined
functions are created by users in database. 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:
Create function fnGetStudent_Info
(
@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).
