USP_DATAFORMTEMPLATE_ADD_COURSE

The save procedure used by the add dataform template "Course Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@COURSEID	nvarchar(12)	IN	Course ID
@NAME	nvarchar(60)	IN	Course name
@DESCRIPTION	nvarchar(1024)	IN	Description
@SCHOOLID	uniqueidentifier	IN	School
@COURSEGRADELEVELS	xml	IN	Grade Levels
@SELECTEDTYPESCODE	tinyint	IN	Course types
@COURSETYPES	xml	IN
@SELECTEDDEPARTMENTSCODE	tinyint	IN	Departments
@DEPARTMENTS	xml	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_COURSE
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @COURSEID nvarchar(12),
    @NAME nvarchar(60),
    @DESCRIPTION nvarchar(1024) = '',
    @SCHOOLID uniqueidentifier = null,
    @COURSEGRADELEVELS xml = null,
    @SELECTEDTYPESCODE tinyint = null,
    @COURSETYPES xml = null,
    @SELECTEDDEPARTMENTSCODE tinyint = 2,
    @DEPARTMENTS xml = null    
)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

--default based on school

declare @GENDERCODE tinyint
select 
  @GENDERCODE = GENDERCODE
from
  dbo.SCHOOL
where
  ID = @SCHOOLID

begin try
    -- handle inserting the data

    insert into dbo.COURSE
        (ID, NAME, DESCRIPTION, COURSEID, SCHOOLID, GENDERCODE, SELECTEDTYPESCODE, SELECTEDDEPARTMENTSCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values
        (@ID, @NAME, @DESCRIPTION, @COURSEID, @SCHOOLID, @GENDERCODE, @SELECTEDTYPESCODE, @SELECTEDDEPARTMENTSCODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

    -- link grade levels

    exec dbo.USP_COURSE_GETGRADELEVELS_ADDFROMXML @ID, @COURSEGRADELEVELS, @CHANGEAGENTID;    
    if not exists(select ID from dbo.COURSEGRADELEVEL where COURSEID = @ID)
        raiserror('CK_COURSE_ATLEASTONEGRADELEVEL', 13, 1)

    --selected course types

    if @SELECTEDTYPESCODE = 1
    begin
        exec dbo.USP_COURSE_GETTYPES_ADDFROMXML @ID, @COURSETYPES, @CHANGEAGENTID;
        if not exists(select ID from dbo.COURSETYPE where COURSEID = @ID)
            raiserror('CK_COURSETYPE_ATLEASTONETYPE', 13, 1)        
    end

    --selected course types

    if @SELECTEDDEPARTMENTSCODE = 1
    begin
        exec dbo.USP_COURSE_GETDEPARTMENTS_ADDFROMXML @ID, @DEPARTMENTS, @CHANGEAGENTID;
        if not exists(select ID from dbo.COURSEACADEMICCATALOGDEPARTMENT where COURSEID = @ID)
            raiserror('CK_COURSEDEPARTMENT_ATLEASTONEDEPARTMENT', 13, 1)        
    end
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0