USP_DATAFORMTEMPLATE_EDIT_CMSQUERYPUBLISHING

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CMSQUERYPUBLISHING(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @PUBLISHED bit
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try

        declare @EXISTINGID uniqueidentifier;
        select @EXISTINGID = CMSQUERYPUBLISHING.ID from dbo.CMSQUERYPUBLISHING where CMSQUERYPUBLISHING.ID = @ID;        

        if @EXISTINGID is null
            -- handle inserting the data
            insert into dbo.CMSQUERYPUBLISHING
            (
                ID,
                PUBLISHED,
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            values
            (
                @ID,
                @PUBLISHED,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            )
        else
            -- handle updating the data
            update dbo.CMSQUERYPUBLISHING set
                PUBLISHED = @PUBLISHED,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @ID

        --Clear cache for this query
        declare @KEY nvarchar(66) = 'ADHOCQUERY_ID_' + cast(@ID as nvarchar(65))
        exec spDelete_CacheItem @KEY, 0

        --Clear cache data that is based on all queries
        exec spDelete_CacheItem 'ADHOCQUERY_ANY', 0

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;