USP_SMARTFIELDCATALOG_CREATEORUPDATE

Creates or updates smart field definitions in the SmartFieldCatalog table.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@PROCEDURENAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(max)	IN
@SMARTFIELDSPECXML	xml	IN
@FORMMETADATAXML	xml	IN
@DATATYPECODE	tinyint	IN
@RECORDTYPEID	uniqueidentifier	IN
@DISPLAYNAME	nvarchar(100)	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SMARTFIELDFOLDER	nvarchar(500)	IN
@VALUERECORDTYPEID	uniqueidentifier	IN
@HASSITEFILTER	bit	IN

Definition

Copy



CREATE procedure dbo.USP_SMARTFIELDCATALOG_CREATEORUPDATE
        @ID uniqueidentifier = null output, 
        @PROCEDURENAME nvarchar(100),
        @DESCRIPTION nvarchar(max) = '',
        @SMARTFIELDSPECXML xml, 
        @FORMMETADATAXML xml,
        @DATATYPECODE tinyint,
        @RECORDTYPEID  uniqueidentifier = null,
        @DISPLAYNAME nvarchar(100),
        @CHANGEAGENTID uniqueidentifier = null,
        @SMARTFIELDFOLDER nvarchar(500) = '',
        @VALUERECORDTYPEID uniqueidentifier = null,
        @HASSITEFILTER bit = 0
    as

    set nocount on

    declare @ExistingID uniqueidentifier

    if @ID is null
        begin
          select @ExistingID = ID from dbo.SMARTFIELDCATALOG where PROCEDURENAME = @PROCEDURENAME
          set @ID = @ExistingID
        end
    else
        select @ExistingID = ID from dbo.SMARTFIELDCATALOG where ID = @ID

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

    declare @date datetime
    set @date = getDate()

    if @ExistingID is null 

        begin    
            if @ID is null set @ID = NewID()

            insert into dbo.SMARTFIELDCATALOG
                (ID, PROCEDURENAME, DESCRIPTION, SMARTFIELDSPECXML, FORMMETADATAXML, DATATYPECODE, RECORDTYPEID, DISPLAYNAME, SMARTFIELDFOLDER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, VALUERECORDTYPEID, HASSITEFILTER)
            values 
                (@ID, @PROCEDURENAME, @DESCRIPTION, @SMARTFIELDSPECXML, @FORMMETADATAXML, @DATATYPECODE, @RECORDTYPEID, @DISPLAYNAME, @SMARTFIELDFOLDER, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE, @VALUERECORDTYPEID, @HASSITEFILTER)
        end

    else
        update 
            dbo.SMARTFIELDCATALOG
        set 
            PROCEDURENAME = @PROCEDURENAME,
            DESCRIPTION = @DESCRIPTION,
            SMARTFIELDSPECXML = @SMARTFIELDSPECXML,
            FORMMETADATAXML = @FORMMETADATAXML, 
            DATATYPECODE = @DATATYPECODE, 
            RECORDTYPEID = @RECORDTYPEID, 
            DISPLAYNAME = @DISPLAYNAME,    
            SMARTFIELDFOLDER = @SMARTFIELDFOLDER,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @DATE,
            VALUERECORDTYPEID = @VALUERECORDTYPEID,
            HASSITEFILTER = @HASSITEFILTER
        where 
            ID = @ID;

    if @@rowcount=0 return 1;

    return 0;