SQL 2008 Using Scalar valued Function and View together

Scalar Function

USE [dbRegistration]
GO
/****** Object: UserDefinedFunction [dbo].[GetAliasesById] Script Date: 11/26/2011 09:50:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetAliasesById]
(
@userID int,
@TermId varchar(50),
@CourseType varchar(20),
@RegularBacklogStatus varchar(10)
)
--select dbo.GetAliasesById(200207007,'CSE2011NOV_2K11Y2T1','Theory','R')
RETURNS varchar(max)
AS
BEGIN
declare @output varchar(max),
@CourseCode varchar(20)

set @output = ''
set @CourseCode = ''

declare cursorname cursor for
select distinct CourseCode
from vwConsolidatedRegistrationSheet
where StudentId = @userID
and CourseType = @CourseType
and RegularBacklogStatus = @RegularBacklogStatus
and TermId=@TermId
open cursorname
fetch next from cursorname
into @CourseCode
while @@FETCH_STATUS = 0
begin
set @output = @output + @CourseCode + ', '
fetch next from cursorname
into @CourseCode
end
close cursorname
deallocate cursorname
if(len(@output) > 0)
SET @output = LEFT(@output,LEN(@output)-1)
return @output
END

SQL view

SELECT DISTINCT
TermId, StudentId, dbo.GetAliasesById(StudentId, TermId, 'Theory', 'R') AS TheoryCorse, dbo.GetAliasesById(StudentId, TermId, 'Sessional', 'R') AS SessionalCourse,
dbo.GetAliasesById(StudentId, TermId, 'Theory', 'B') AS TheoryBacklogCorse, dbo.GetAliasesById(StudentId, TermId, 'Sessional', 'B') AS SessionalBacklogCourse,
CreditTaken, TargetTerm, TargetYear, departmentName, AcademicYear
FROM dbo.vwConsolidatedRegistrationSheet

SELECT dbo.T_Term_Std_Course_Reg.StudentId, dbo.T_Std_General_Information.StudentsName, dbo.T_Term_Std_Course_Reg.CourseId,
dbo.T_Common_Course_Information.CourseCode, dbo.T_Common_Course_Information.CourseTitle, dbo.T_Common_Course_Information.CourseCredit,
dbo.T_Common_Course_Information.CourseType, dbo.T_Term_Setup.DepartmentId, dbo.T_Common_Department_Setup.departmentName,
dbo.T_Term_Setup.TargetYear, dbo.T_Term_Setup.TargetTerm, dbo.T_Term_Setup.TermClosingStatus, dbo.T_Term_Std_Course_Reg.RegularBacklogStatus,
dbo.T_Term_Std_Course_Reg.TermId, dbo.T_Term_Student_Reg_Status.CreditTaken, dbo.T_Term_Setup.AcademicYear
FROM dbo.T_Common_Course_Information INNER JOIN
dbo.T_Term_Std_Course_Reg ON dbo.T_Common_Course_Information.CourseId = dbo.T_Term_Std_Course_Reg.CourseId INNER JOIN
dbo.T_Std_General_Information ON dbo.T_Term_Std_Course_Reg.StudentId = dbo.T_Std_General_Information.StudentId INNER JOIN
dbo.T_Term_Setup ON dbo.T_Term_Std_Course_Reg.TermId = dbo.T_Term_Setup.TermId INNER JOIN
dbo.T_Common_Department_Setup ON dbo.T_Term_Setup.DepartmentId = dbo.T_Common_Department_Setup.departmentId INNER JOIN
dbo.T_Term_Student_Reg_Status ON dbo.T_Term_Std_Course_Reg.TermId = dbo.T_Term_Student_Reg_Status.TermId

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