USP_CLASSCATEGORY_EDIT

CREATE procedure dbo.USP_CLASSCATEGORY_EDIT
(
    @CLASSID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CATEGORIES xml
)
as
    set nocount on;

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

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    begin try
        declare @temp table (
            ID uniqueidentifier NOT NULL,
            CLASSID uniqueidentifier NOT NULL,
            NAME nvarchar(60) NOT NULL,
            DEFAULTNAME nvarchar(60) NOT NULL,
            ISGRADED bit NOT NULL,
            GRADINGSCALEID uniqueidentifier NULL,
            MAXPOINTS decimal(14,2) NOT NULL,
            AVERAGINGMETHODCODE tinyint NOT NULL,
            TOTALGRADEPERCENTAGE decimal(14,2) NOT NULL
        );

        insert into @temp (
            ID,
            CLASSID,
            NAME,
            DEFAULTNAME,
            ISGRADED,
            GRADINGSCALEID,
            MAXPOINTS,
            AVERAGINGMETHODCODE,
            TOTALGRADEPERCENTAGE)
        select
            isnull(ID, NEWID()),
            @CLASSID,
            NAME,
            DEFAULTNAME,
            ISGRADED,
            GRADINGSCALEID,
            MAXPOINTS,
            AVERAGINGMETHODCODE,
            TOTALGRADEPERCENTAGE
        from dbo.UFN_CLASS_GETCATEGORIES_FROMITEMLISTXML(@CATEGORIES)

        if (select count(ID) from @temp) = 0 and (select USECATEGORYPERCENTAGES from dbo.CLASS where ID = @CLASSID) = 1
            raiserror('BBERR_CLASSCATEGORY_PERCENTAGESNOTEQUALHUNDRED', 13,1);

        --Cache current context information 

        declare @CONTEXTCACHE varbinary(128); 
        set @CONTEXTCACHE = CONTEXT_INFO(); 
        set CONTEXT_INFO @CHANGEAGENTID; 

        merge dbo.CLASSCATEGORY as target
        using (select
                ID,
                CLASSID,
                NAME,
                DEFAULTNAME,
                ISGRADED,
                GRADINGSCALEID,
                MAXPOINTS,
                AVERAGINGMETHODCODE,
                TOTALGRADEPERCENTAGE
                from @temp) as source (
                    ID,
                    CLASSID,
                    NAME,
                    DEFAULTNAME,
                    ISGRADED,
                    GRADINGSCALEID,
                    MAXPOINTS,
                    AVERAGINGMETHODCODE,
                    TOTALGRADEPERCENTAGE)
        on (target.ID = source.ID and target.CLASSID = @CLASSID)
        when not matched by source and target.CLASSID = @CLASSID then DELETE
        when matched then
            update set
                ID = source.ID,
                CLASSID = source.CLASSID,
                NAME = source.NAME,
                DEFAULTNAME = source.DEFAULTNAME,
                ISGRADED = source.ISGRADED,
                GRADINGSCALEID = source.GRADINGSCALEID,
                MAXPOINTS = source.MAXPOINTS,
                AVERAGINGMETHODCODE = source.AVERAGINGMETHODCODE,
                TOTALGRADEPERCENTAGE = source.TOTALGRADEPERCENTAGE,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
        when not matched then
            insert (
                ID,
                CLASSID,
                NAME,
                DEFAULTNAME,
                ISGRADED,
                GRADINGSCALEID,
                MAXPOINTS,
                AVERAGINGMETHODCODE,
                TOTALGRADEPERCENTAGE,
                ADDEDBYID,
                DATEADDED,
                CHANGEDBYID,
                DATECHANGED)
            values (
                source.ID,
                source.CLASSID,
                source.NAME,
                source.DEFAULTNAME,
                source.ISGRADED,
                source.GRADINGSCALEID,
                source.MAXPOINTS,
                source.AVERAGINGMETHODCODE,
                source.TOTALGRADEPERCENTAGE,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CHANGEAGENTID,
                @CURRENTDATE);

       if not @CONTEXTCACHE is null 
        set CONTEXT_INFO @CONTEXTCACHE;
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0;