Home > DeveloperSection > Blogs > User Defined function in SQL

User Defined function in SQL


MSSQL Server SQL  SQL Server 2008 
Ratings:
0 Comment(s)
 151  View(s)
Rate this:

User Defined function in SQL

There are three ways by which we can create a user defined function:

Scalar function

In this type of function single return value will achieved. It is similar to use Count, avg etc system defined function.

E.g.: creating a function to calculate grade of a students.

Here is the small program which may help you to understand the basic of this type:

CREATE FUNCTION
GRADE
(
@NUM INT
)
RETURNS CHAR(5)
AS
BEGIN
 
DECLARE @P CHAR(5)
 
if (@NUM>90)
begin
SET @P='A'
end
 
else if @NUM<90  AND @num>80
begin
SET @P='B'
end
else if @NUM>70 AND @NUM <80
begin
SET @P='C'
end   
ELSE
BEGIN
SET @p='D'
END
 
RETURN (select @P)
END
 

Inline table-valued function:

In the above example, we have found the grade of a student, by inline valued function

we can get multiple rows just by entering a single value.

For example we need a record for the student whose grade is ‘A’.

Here is the small program to understand this concept.

E.g.: creating a function to calculate grade of a students.

Here is the small program which may help you to understand the basic of this type:

CREATE FUNCTION
GRADE1
(
@entry char(3)
)
RETURNS table AS
 
return
select student_id,name,marks,dbo.grade(marks)as grade1
from Student_Dt
group by student_id,name,marks
having dbo.grade(marks)=@entry
go Syntax to call the function: Select * from functionname(‘object’)

Multi Statement Table-Valued Function:

Result is same when we compare to Inline table-valued function. Then why we need to use this form of values user defined function? Inline table-valued function can be simply designed but when program is large enough and time consumed by the program is considerable then Inline table-valued function is not used because it will take very much time to perform it’s operation. Whereas multi-statement Table-Valued Function is 10 times faster than Inline table-valued function. Hence as a good programmer one should always use Multi Statement Table-Valued Function and avoid using Inline table-valued function.

 

Here is the small program which may help you to understand the basic of this type:


Create FUNCTION GRADE3
(
@entry char(3)
)
RETURNS @grade3 table
(
stdid int,
grade char
)
AS
begin
insert into @grade3
select student_id,dbo.GRADE(marks)
from Student_Dt
group by student_id,name,marks
having dbo.GRADE(marks)=@entry
 
return
end

Syntax to call the function:

Select * from functionname(‘object’)


Don't want to miss updates? Please click the below button!

Follow MindStick