USP_KPICATALOG_CREATEORUPDATE

Adds a KPI spec to the database or updates an existing KPI spec.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@NAME	nvarchar(255)	IN
@DESCRIPTION	nvarchar(max)	IN
@IMPLEMENTATIONTYPECODE	tinyint	IN
@ASSEMBLYNAME	nvarchar(100)	IN
@CLASSNAME	nvarchar(150)	IN
@SPNAME	nvarchar(100)	IN
@GOALSPNAME	nvarchar(100)	IN
@GOALTYPECODE	tinyint	IN
@GOALRETRIEVALTYPECODE	tinyint	IN
@UIFOLDER	nvarchar(255)	IN
@SECURITYUIFOLDER	nvarchar(255)	IN
@SPECXML	xml	IN
@CONTEXTRECORDTYPE	nvarchar(255)	IN
@CONTEXTRECORDIDPARAMETER	nvarchar(255)	IN
@CHANGEAGENTID	uniqueidentifier	IN
@DECIMALPLACES	tinyint	IN
@HASSITEFILTER	bit	IN

Definition

Copy



CREATE procedure dbo.USP_KPICATALOG_CREATEORUPDATE(@ID uniqueidentifier,
    @NAME nvarchar(255),
    @DESCRIPTION nvarchar(max),
    @IMPLEMENTATIONTYPECODE tinyint,
    @ASSEMBLYNAME nvarchar(100),
    @CLASSNAME nvarchar(150),
    @SPNAME nvarchar(100),
    @GOALSPNAME nvarchar(100),
    @GOALTYPECODE tinyint,
    @GOALRETRIEVALTYPECODE tinyint,
    @UIFOLDER nvarchar(255),
    @SECURITYUIFOLDER nvarchar(255),
    @SPECXML xml,
    @CONTEXTRECORDTYPE nvarchar(255),
    @CONTEXTRECORDIDPARAMETER nvarchar(255),
    @CHANGEAGENTID uniqueidentifier,
    @DECIMALPLACES tinyint = 0,
    @HASSITEFILTER bit = 0)
as
    set nocount on;

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

    declare @CONTEXTRECORDTYPEID uniqueidentifier;

    if len(@CONTEXTRECORDTYPE) > 0
    begin
        exec dbo.USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME @NAME = @CONTEXTRECORDTYPE, @CHANGEAGENTID = @CHANGEAGENTID, @RECORDTYPEID = @CONTEXTRECORDTYPEID output;
    end;

    if exists(select ID from dbo.KPICATALOG where ID = @ID)
    begin
        update dbo.KPICATALOG set NAME = @NAME,
            DESCRIPTION = @DESCRIPTION,
            IMPLEMENTATIONTYPECODE = @IMPLEMENTATIONTYPECODE,
            ASSEMBLYNAME = @ASSEMBLYNAME,
            CLASSNAME = @CLASSNAME,
            SPNAME = @SPNAME,
            GOALSPNAME = @GOALSPNAME,
            GOALTYPECODE = @GOALTYPECODE,
            GOALRETRIEVALTYPECODE = @GOALRETRIEVALTYPECODE,
            UIFOLDER = @UIFOLDER,
            SECURITYUIFOLDER = @SECURITYUIFOLDER,
            SPECXML = @SPECXML,
            CONTEXTRECORDTYPEID = @CONTEXTRECORDTYPEID,
            CONTEXTRECORDIDPARAMETER = @CONTEXTRECORDIDPARAMETER,
            DATECHANGED = getdate(),
            CHANGEDBYID = @CHANGEAGENTID,
            DECIMALPLACES = @DECIMALPLACES,
            HASSITEFILTER = @HASSITEFILTER
        where ID = @ID;
    end
    else
    begin
        insert into dbo.KPICATALOG (ID,
            NAME,
            DESCRIPTION,
            IMPLEMENTATIONTYPECODE,
            ASSEMBLYNAME,
            CLASSNAME,
            SPNAME,
            GOALSPNAME,
            GOALTYPECODE,
            GOALRETRIEVALTYPECODE,
            UIFOLDER,
            SECURITYUIFOLDER,
            SPECXML,
            CONTEXTRECORDTYPEID,
            CONTEXTRECORDIDPARAMETER,
            DATEADDED,
            DATECHANGED,
            ADDEDBYID,
            CHANGEDBYID,
            DECIMALPLACES,
            HASSITEFILTER)
        values (@ID,
            @NAME,
            @DESCRIPTION,
            @IMPLEMENTATIONTYPECODE,
            @ASSEMBLYNAME,
            @CLASSNAME,
            @SPNAME,
            @GOALSPNAME,
            @GOALTYPECODE,
            @GOALRETRIEVALTYPECODE,
            @UIFOLDER,
            @SECURITYUIFOLDER,
            @SPECXML,
            @CONTEXTRECORDTYPEID,
            @CONTEXTRECORDIDPARAMETER,
            getdate(),
            getdate(),
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @DECIMALPLACES,
            @HASSITEFILTER);
    end;