User Defined Function in Sql Server Part-I

A 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. 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)
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

(B) Implementation of function
insert into tbl_student(Name,Registration_No) values(‘Shekhawat’,dbo.Registration_Number())


Student_id Name Registration_No
1 Shekhawat STU0

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: