Different Kinds of User-Defined Functions created are:
- Scalar User-Defined Function
- Inline Table-Value User-Defined Function
- 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. These are the type of user-defined functions that most developers are used to in other programming languages. 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())
OutPut
Student_id | Name | Registration_No |
---|---|---|
1 | Shekhawat | STU0 |