Scalar User Defined Function in Sql Server

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
For read it Click Here 


Next Recommended Reading User-Defined Functions in SQL Server