Types of SQL functions with examples

Mainly 3 types of SQL functions: (I) Table-valued function: returns a table, (II) Scalar-valued function: returns single value and (III) Aggregate function: SUM, COUNT, AVG, MAX, MIN etc.

Table valued function:

USE [dbRegistration]
GO
/****** Object: UserDefinedFunction [dbo].[fn_StudentRegStatus] Script Date: 12/04/2011 10:07:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_StudentRegStatus]
(
@StudentId1 int
)
--select * from dbo.fn_StudentRegStatus(2)
RETURNS @StudentRegStatus TABLE
(
StudentId int,
DeptId varchar(50),
CourseCode1 varchar(50),
CourseCode2 varchar(50),
CourseCode3 varchar(50),
CourseCode4 varchar(50),
CourseCode5 varchar(50),
CourseCode6 varchar(50)
)
BEGIN
DECLARE @Cols NVARCHAR(2000),
@Parameter NVARCHAR(2000),
@StudentId int,
@DeptId varchar(50),
@CourseCode1 varchar(50),
@CourseCode2 varchar(50),
@CourseCode3 varchar(50),
@CourseCode4 varchar(50),
@CourseCode5 varchar(50),
@CourseCode6 varchar(50)
SET @Cols = ''
set @Parameter = '@Cols NVARCHAR(2000)'
--collect distinct Course Code that we have in tblStudentResult
SELECT @Cols = @Cols + '[' + CourseCode + ']' + ', ' FROM
(SELECT DISTINCT isnull(CourseCode,'N/A') as CourseCode FROM
vwConsolidatedRegistrationSheet ) AS s order by s.CourseCode
SET @Cols = LEFT(@Cols,LEN(@Cols)-1)
declare @sqlstatement nvarchar(4000)
--move declare cursor into sql to be executed
set @sqlstatement = 'declare cursorname cursor for SELECT * FROM (SELECT StudentId, CourseCode, RegularBacklogStatus FROM
vwConsolidatedRegistrationSheet ) ps PIVOT ( MAX(RegularBacklogStatus) FOR CourseCode IN (
'+@cols+') ) AS pvt'
exec sp_executesql @sqlstatement,@Parameter ,@cols
open cursorname
fetch next from cursorname
into @StudentId,@CourseCode1,@CourseCode2 ,@CourseCode3 ,@CourseCode4 ,@CourseCode5 ,@CourseCode6
while @@FETCH_STATUS = 0
begin
--INSERT INTO @StudentRegStatus(StudentId,CourseCode1,CourseCode2,CourseCode3,CourseCode4,CourseCode5,CourseCode6)
--values(@StudentId,@CourseCode1,@CourseCode2,@CourseCode3,@CourseCode4,@CourseCode5,@CourseCode6)
fetch next from cursorname
into @StudentId,@CourseCode1,@CourseCode2 ,@CourseCode3 ,@CourseCode4 ,@CourseCode5 ,@CourseCode6
end
close cursorname
deallocate cursorname
RETURN
END

Scalar valued function:

USE [dbRegistration]
GO
/****** Object: UserDefinedFunction [dbo].[fn_BacklogCourse] Script Date: 12/04/2011 10:14:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_BacklogCourse]
(
@StudentId int,
@DepartmentId varchar(50)
)
--select[dbo].fn_BacklogCourse(201007007,'CSE')
--select dbo.fn_BacklogCourse(201007007,'CSE')
returns varchar(200)
as
begin
declare @name varchar(200),
@QuoteVariable varchar(2)
SELECT @QuoteVariable=''
set @name = ''
SELECT @name = @name + CourseCode + ', ' FROM
(SELECT DISTINCT T_Term_Course_Offer.CourseCode
FROM T_Term_Wise_Course_Result LEFT OUTER JOIN T_Term_Course_Offer ON
T_Term_Wise_Course_Result.CourseId = T_Term_Course_Offer.CourseId
LEFT OUTER JOIN T_Term_Setup ON T_Term_Setup.TermId=T_Term_Course_Offer.TermId
WHERE T_Term_Wise_Course_Result.StudentId=@StudentId AND T_Term_Wise_Course_Result.LetterGrade='F'
AND (T_Term_Wise_Course_Result.CourseId NOT IN(SELECT CourseId from T_Common_Student_Earned_Credit WHERE StudentId = @StudentId))
AND T_Term_Course_Offer.DepartmentId=@QuoteVariable+@DepartmentId+@QuoteVariable )
AS s order by s.CourseCode
SET @name = LEFT(@name,LEN(@name)-1)
return @name
end

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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