USP_EXPORTDEFINITION_CREATEORUPDATEDEFINITION

Creates or updated an export definition.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@NAME	nvarchar(200)	IN
@DESCRIPTION	nvarchar(1000)	IN
@EXPORTDEFINITIONXML	xml	IN
@RECORDTYPEID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SITEID	uniqueidentifier	IN
@LOCKFIELDS	bit	IN
@ISSYSTEM	bit	IN
@QUERYVIEWCATALOGID	uniqueidentifier	IN
@USEFIELDNAMESFORONETOONECOLUMNHEADERS	bit	IN
@USESHORTCOLUMNHEADERS	bit	IN
@OUTPUTCOLUMNMETADATA	xml	IN

Definition

Copy



CREATE procedure dbo.[USP_EXPORTDEFINITION_CREATEORUPDATEDEFINITION]
(
  @ID uniqueidentifier,
  @NAME nvarchar(200),
  @DESCRIPTION nvarchar(1000),
  @EXPORTDEFINITIONXML xml,
  @RECORDTYPEID as uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @SITEID uniqueidentifier = null,
  @LOCKFIELDS bit = null,
  @ISSYSTEM bit = null,
  @QUERYVIEWCATALOGID uniqueidentifier = null,
  @USEFIELDNAMESFORONETOONECOLUMNHEADERS bit = 0,
  @USESHORTCOLUMNHEADERS bit = 1,
  @OUTPUTCOLUMNMETADATA xml = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime = getdate();
  declare @EXISTINGID uniqueidentifier;

  select @EXISTINGID = EXPORTDEFINITION.ID from dbo.EXPORTDEFINITION where EXPORTDEFINITION.ID = @ID;

  if @EXISTINGID is null
    begin
      insert into dbo.EXPORTDEFINITION(ID, NAME, DESCRIPTION, SITEID, EXPORTDEFINITIONXML, RECORDTYPEID, LOCKFIELDS, ISSYSTEM, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, QUERYVIEWCATALOGID, USEFIELDNAMESFORONETOONECOLUMNHEADERS, USESHORTCOLUMNHEADERS, OUTPUTCOLUMNMETADATA)
        values (@ID, @NAME, @DESCRIPTION, @SITEID, @EXPORTDEFINITIONXML, @RECORDTYPEID, isnull(@LOCKFIELDS, 0), isnull(@ISSYSTEM, 0), @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @QUERYVIEWCATALOGID, @USEFIELDNAMESFORONETOONECOLUMNHEADERS, @USESHORTCOLUMNHEADERS, @OUTPUTCOLUMNMETADATA);
    end
  else
    begin
      update dbo.EXPORTDEFINITION set    
        NAME = @NAME,
        DESCRIPTION = @DESCRIPTION,
        EXPORTDEFINITIONXML = @EXPORTDEFINITIONXML,
        RECORDTYPEID= @RECORDTYPEID,
        --OTHERSCANMODIFY = @OTHERSCANMODIFY,

        SITEID = @SITEID,
        --SECURITYLEVEL = @SECURITYLEVEL,

        --SECURITYLEVELEDIT = @SECURITYLEVELEDIT,

        LOCKFIELDS = isnull(@LOCKFIELDS, LOCKFIELDS),
        ISSYSTEM = isnull(@ISSYSTEM, ISSYSTEM),
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE,
                USEFIELDNAMESFORONETOONECOLUMNHEADERS = @USEFIELDNAMESFORONETOONECOLUMNHEADERS,
                USESHORTCOLUMNHEADERS = @USESHORTCOLUMNHEADERS,
        OUTPUTCOLUMNMETADATA = @OUTPUTCOLUMNMETADATA
      where ID = @ID;

            update 
        dbo.EXPORTDEFINITION 
      set    
        QUERYVIEWCATALOGID = @QUERYVIEWCATALOGID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        ID = @ID and QUERYVIEWCATALOGID is null;

    end

  return 0;