USP_SELECTIONMERGE_CREATEORUPDATE

CREATE procedure dbo.[USP_SELECTIONMERGE_CREATEORUPDATE]
(
  @ID uniqueidentifier = null output,
  @RECORDTYPEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(300),
  @DESCRIPTION nvarchar(1024) = '',
  @SELECTIONID1 uniqueidentifier,
  @SELECTIONID2 uniqueidentifier,
  @MERGETYPECODE tinyint = 0,
  @IDSETREGISTERID uniqueidentifier = null,
  @USEINQUERYDESIGNER bit = 0
)
as
  set nocount on;

  declare @FUNCTIONNAME nvarchar(128);
  declare @MERGESQL nvarchar(max);
  declare @SQL nvarchar(max);
  declare @CURRENTDATE datetime;

  begin try       
    if @ID is null
      set @ID = newid();

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

    set @CURRENTDATE = getdate();


    set @MERGESQL = dbo.[UFN_SELECTIONMERGE_BUILDIDSELECTSQL](@SELECTIONID1, @SELECTIONID2, @MERGETYPECODE);
    if @MERGESQL is null
      raiserror('Invalid merge type, or one of the selections does not exist in the database.', 13, 1);


    --Build the sql for the table-value function...

    set @FUNCTIONNAME = 'UFN_SELECTIONMERGE_' + replace(convert(nvarchar(36), @ID), '-', '_');
    if exists(select * from INFORMATION_SCHEMA.ROUTINES where [ROUTINE_SCHEMA] = 'dbo' and [ROUTINE_NAME] = @FUNCTIONNAME and [ROUTINE_TYPE] = 'FUNCTION')
      set @SQL = 'alter';
    else
      set @SQL = 'create';

    set @SQL += ' function dbo.[' + @FUNCTIONNAME + ']()' + char(13) +
                'returns table' + char(13) +
                'as' + char(13) +
                'return (' + char(13) +
                @MERGESQL +
                ');';
    exec (@SQL);


    --Create the IDSet record information...

    exec dbo.[USP_IDSETREGISTER_CREATEORUPDATE] @IDSETREGISTERID output, @NAME, @DESCRIPTION, @FUNCTIONNAME, 1, @RECORDTYPEID, @USEINQUERYDESIGNER, 0, null, @CHANGEAGENTID, null, null;

    if exists(select * from dbo.[IDSETREGISTERMERGE] where [ID] = @ID)
      update dbo.[IDSETREGISTERMERGE] set
        [NAME] = @NAME,
        [IDSETREGISTERID] = @IDSETREGISTERID,
        [SELECTION1ID] = @SELECTIONID1,
        [SELECTION2ID] = @SELECTIONID2,
        [MERGETYPECODE] = @MERGETYPECODE,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @ID;
    else
      insert into dbo.[IDSETREGISTERMERGE] (
        [ID],
        [NAME],
        [IDSETREGISTERID],
        [SELECTION1ID],
        [SELECTION2ID],
        [MERGETYPECODE],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
      ) values (
        @ID,
        @NAME,
        @IDSETREGISTERID,
        @SELECTIONID1,
        @SELECTIONID2,
        @MERGETYPECODE,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;