User-defined function (UDF) is
a prepared code segment that can accept parameters ( from 0 to 1024), process
some logic, and then return some data (either a scalar value or a table).
Different Kinds of
User-Defined Functions created are:
1.
Scalar User-Defined
Function
2.
Inline Table-Value
User-Defined Function
3.
Multi-statement
Table-Value User-Defined Function
Scalar
User-Defined Function
A Scalar user-defined function
returns one of the scalar data types. Text, ntext, image and timestamp data
types are not supported. You pass in 0 to many parameters and you get a return
value.
Illustration with
an Example
We have a table tbl_student. We want generate a registration number for each
new student that admission in college.So we create a function dbo.Registration_Number()
that has return type varchar(50) which is a registration number for new student
Student
Table(tbl_student):
|
Column Name
|
Data Type
|
Constraint/Description
|
|
Student_id
|
int
|
Identity(1,1) Primary Key
|
|
Name
|
varchar(50)
|
Name of student
|
|
Registration_No
|
varchar(50)
|
New Registration no for each student
|
(A)
Create a function for Registration Number
create function Registration_Number()
returns varchar(50)
as
begin
declare @totalrow int
declare @reg_no varchar(50)
select @totalrow=count(*) from tbl_student
set @reg_no='STU'+cast(@totalrow as varchar(5))
return @reg_no
end
(B)
Implementation of function
insert into
tbl_student(Name,Registration_No) values('Shekhawat',dbo.Registration_Number())
(C)
OutPut
select *
from tbl_student
|
Student_id
|
Name
|
Registration_No
|
|
1
|
Shekhawat
|
STU0
|
Inline Table-Value User-Defined Function