spUpdateSiteContentStatus

    CREATE procedure [dbo].[spUpdateSiteContentStatus]
    (
        @SiteContentID int,
        @Status int,
        @StatusUpdatedBy int,
        @Comments nvarchar(256)
    )
        as
        begin
            if @Status=2 and exists(select null from dbo.SiteContentVersion where [Status] in (0,1) and [SiteContentID]=@SiteContentID)
                /* archive live version first */
                update dbo.SiteContentVersion set [Status]=3, [StatusUpdatedOn]=getutcdate(), [StatusUpdatedBy]=@StatusUpdatedBy, [Comments]=@Comments where [Status]=@Status and [SiteContentID]=@SiteContentID

            /* update status on draft/pending version accordingly */
            update dbo.SiteContentVersion set [Status]=@Status, [StatusUpdatedOn]=getutcdate(), [StatusUpdatedBy]=@StatusUpdatedBy, [Comments]=@Comments where [Status] in (0,1) and [SiteContentID]=@SiteContentID

            if @Status=4    /* rejection */
                if exists(select null from dbo.SiteContentVersion where [SiteContentID]=@SiteContentID and [Status]=2)
                    /* restore xmldata on sitecontent record */
                    update dbo.SiteContent set [XMLData]=(select [XMLData] from dbo.SiteContentVersion where [SiteContentID]=@SiteContentID and [Status]=2) where [ID]=@SiteContentID
                else
                    /* if a new part is rejected, generate a new draft */
                    insert into dbo.SiteContentVersion (SiteContentID, [XMLData], [Status], [StatusUpdatedBy]) select [ID], [XMLData], 0, @StatusUpdatedBy from dbo.SiteContent where [ID]=@SiteContentID
        end