USP_DATAFORMTEMPLATE_VIEW_SESSION

The load procedure used by the view dataform template "Session profile view"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TIMETABLENAME	nvarchar(100)	INOUT	Timetable
@MARKING_COLUMNS	nvarchar(100)	INOUT	Marking columns
@WEEKDAYS	nvarchar(100)	INOUT	Days in session
@ACADEMICYEAR_NAME	nvarchar(100)	INOUT	Academic year
@ACADEMICYEAR_STARTDATE	date	INOUT	ACADEMICYEAR_STARTDATE
@ACADEMICYEAR_ENDDATE	date	INOUT	ACADEMICYEAR_ENDDATE
@SCHOOLNAME	nvarchar(100)	INOUT	School

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SESSION
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @TIMETABLENAME nvarchar(100) = null output,
                    @MARKING_COLUMNS nvarchar(100) = null output,
                    @WEEKDAYS nvarchar(100) = null output,
                    @ACADEMICYEAR_NAME nvarchar(100) = null output,
                    @ACADEMICYEAR_STARTDATE date = null output,
                    @ACADEMICYEAR_ENDDATE date = null output,
                    @SCHOOLNAME nvarchar(100) = null output
                )
                as
                    set nocount on

                    -- be sure to set this, in case the select returns no rows

                    set @DATALOADED = 0

                    -- populate the output parameters, which correspond to fields on the form.  Note that

                    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

                    -- will display a "no data loaded" message.

                    select @DATALOADED = 1,
                                 @TIMETABLENAME = TIMETABLE.NAME,
                                 @MARKING_COLUMNS = (select stuff((select '; ' + MARKINGCOLUMN.DISPLAYNAME
                                       from dbo.MARKINGCOLUMN
                                           join dbo.MARKINGCOLUMNDESCRIPTIONCODE on MARKINGCOLUMNDESCRIPTIONCODE.ID = MARKINGCOLUMN.MARKINGCOLUMNDESCRIPTIONCODEID
                                       where MARKINGCOLUMN.MARKINGCOLUMNSETID = SESSION.MARKINGCOLUMNSETID
                                       order by MARKINGCOLUMN.SEQUENCE
                                       for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')),
                                 @WEEKDAYS = dbo.UFN_SESSION_GETDAYS(SESSION.ID),
                                 @ACADEMICYEAR_NAME = ACADEMICYEARNAMECODE.DESCRIPTION,
                                 @ACADEMICYEAR_STARTDATE = ACADEMICYEAR.STARTDATE,
                                 @ACADEMICYEAR_ENDDATE = ACADEMICYEAR.ENDDATE,
                                 @SCHOOLNAME = CONSTIT_SCHOOL.NAME
                    from dbo.SESSION
                        inner join dbo.SESSIONNAMECODE on SESSIONNAMECODE.ID = SESSION.SESSIONNAMECODEID
                        inner join dbo.TIMETABLE on SESSION.TIMETABLEID = TIMETABLE.ID
                        inner join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
                        inner join dbo.CONSTITUENT CONSTIT_SCHOOL on ACADEMICYEAR.SCHOOLID = CONSTIT_SCHOOL.ID
                        inner join dbo.ACADEMICYEARNAMECODE on ACADEMICYEAR.ACADEMICYEARNAMECODEID = ACADEMICYEARNAMECODE.ID
                    where SESSION.ID = @ID

                    return 0