USP_MKTSEGMENT_CREATEORUPDATEVIEW

CREATE procedure dbo.USP_MKTSEGMENT_CREATEORUPDATEVIEW
(
  @SEGMENTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @USEINQUERYDESIGNER bit = 0
)
with execute as owner
as
  set nocount on;

  declare @CURRENTDATE datetime;

  declare @SEGSQL nvarchar(max);
  declare @VIEWSQL nvarchar(max);
  declare @SEGMENTVIEW nvarchar(60);
  declare @TABLECOUNT int;
  declare @BASETABLE nvarchar(255);
  declare @PREVTABLE nvarchar(255);
  declare @JOINTABLE nvarchar(255);
  declare @SELECTIONNAME nvarchar(300);

  declare @SEGMENTNAME nvarchar(100);
  declare @SEGMENTDESCRIPTION nvarchar(255);
  declare @DATATYPE nvarchar(128);
  declare @SELECTIONID uniqueidentifier;
  declare @IDSETREGISTERID uniqueidentifier;
  declare @RECORDTYPEID uniqueidentifier;
  declare @NEWIDSET bit;

  declare @LOCKRESULT int;
  declare @RETVAL int;

  begin try
    set @TABLECOUNT = 1;
    set @BASETABLE = null;
    set @CURRENTDATE = getdate();


    /**** Get the segment information ****/
    select
      @SEGMENTVIEW = dbo.[UFN_MKTSEGMENT_MAKEVIEWNAME]([MKTSEGMENT].[ID]),
      @SEGMENTNAME = [MKTSEGMENT].[NAME] + ' (Segment)',
      @SEGMENTDESCRIPTION = [MKTSEGMENT].[DESCRIPTION],
      @DATATYPE = [QUERYVIEWCATALOG].[PRIMARYKEYTYPENAME],
      @RECORDTYPEID = [QUERYVIEWCATALOG].[RECORDTYPEID]
    from dbo.[MKTSEGMENT]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
    where [MKTSEGMENT].[ID] = @SEGMENTID;



    /**** Segment selections ****/
    declare SEGMENTCURSOR cursor local fast_forward for
      select [MKTSEGMENTSELECTION].[SELECTIONID]
      from dbo.[MKTSEGMENTSELECTION]
      inner join dbo.[IDSETREGISTER] on [MKTSEGMENTSELECTION].[SELECTIONID] = [IDSETREGISTER].[ID]
      where [MKTSEGMENTSELECTION].[SEGMENTID] = @SEGMENTID;

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @SELECTIONID;

    while (@@FETCH_STATUS = 0)
    begin
      --Remove the CR and LF characters since we are inserting this name into a dash-dash (--) comment in the SQL we are building...

      select @SELECTIONNAME = replace(replace([NAME], char(13), ''), char(10), '')
      from dbo.[IDSETREGISTER]
      where [ID] = @SELECTIONID;

      if @BASETABLE is null
        begin
          set @BASETABLE = '[T' + convert(nvarchar(10), @TABLECOUNT) + ']';
          set @SEGSQL = 'dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@SELECTIONID) + ' as ' + @BASETABLE + '  --' + @SELECTIONNAME + char(13);

          set @PREVTABLE = @BASETABLE;
        end 
      else
        begin
          set @JOINTABLE = '[T' + convert(nvarchar(10), @TABLECOUNT) + ']';
          set @SEGSQL = isnull(@SEGSQL,'') + 'inner join dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@SELECTIONID) + ' as ' + @JOINTABLE + ' on ' + @JOINTABLE + '.[ID] = ' + @BASETABLE + '.[ID]  --' + @SELECTIONNAME + char(13);

          set @PREVTABLE = @JOINTABLE;
        end

      set @TABLECOUNT = @TABLECOUNT + 1;
      fetch next from SEGMENTCURSOR into @SELECTIONID;
    end;

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;



    /**** Get an applock for the segment while we modify its view ****/
    exec @LOCKRESULT = dbo.[USP_MKTSEGMENT_GETAPPLOCK] @SEGMENTID, 0;
    if @LOCKRESULT < 0
      raiserror('A failure or timeout occurred while requesting an app lock to save a segment.', 13, 1);



    /**** Create or alter the segment view ****/
    declare @OBJID int;
    select @OBJID = object_id(@SEGMENTVIEW, N'V');
    if @OBJID is null
      set @VIEWSQL = 'create';
    else
      set @VIEWSQL = 'alter';

    set @VIEWSQL = @VIEWSQL + ' view dbo.[' + @SEGMENTVIEW + ']' + char(13) +
                   'as' + char(13) +
                   'select ' + (case when @BASETABLE is null
                                  then 'top(0) cast(null as ' + @DATATYPE + ') as [ID]'
                                  else @BASETABLE + '.[ID] from ' + @SEGSQL
                                end);

    exec (@VIEWSQL);

    /**** Grant rights for new views ****/
    if @OBJID is null
      exec ('grant select on dbo.[' + @SEGMENTVIEW + '] to BBAPPFXSERVICEROLE');



    /**** Add to the ID Set Register ****/
    -- change agent ID

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

    -- get the view row count

    declare @COUNTSQL nvarchar(max);
    declare @PARAMETERS nvarchar(max);
    declare @NUMROWS int;
    set @COUNTSQL = 'select @NUMROWS = count(*) from dbo.[' + @SEGMENTVIEW + ']';
    set @PARAMETERS = N'@NUMROWS int OUTPUT' 
    exec sp_executeSQL @COUNTSQL, @PARAMETERS, @NUMROWS OUTPUT;

    --Get the ID set ID if this is an edit operation, else it will be null.  Also get the segment record type.

    select distinct
      @IDSETREGISTERID = [MKTSEGMENT].[IDSETREGISTERID],
      @RECORDTYPEID = isnull([IDSETREGISTER].[RECORDTYPEID], @RECORDTYPEID),
      @NEWIDSET = (case when [MKTSEGMENT].[IDSETREGISTERID] is null then 1 else 0 end),
      @USEINQUERYDESIGNER = (case when [MKTSEGMENT].[IDSETREGISTERID] is null then @USEINQUERYDESIGNER else (select [USEINQUERYDESIGNER] from dbo.[IDSETREGISTER] where [ID] = [MKTSEGMENT].[IDSETREGISTERID]) end)
    from dbo.[MKTSEGMENT]
    left join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENT].[ID]
    left join dbo.[IDSETREGISTER] on [MKTSEGMENTSELECTION].[SELECTIONID] = [IDSETREGISTER].[ID]
    where [MKTSEGMENT].[ID] = @SEGMENTID;

    --Create the ID set...

    exec [dbo].[USP_IDSETREGISTER_CREATEORUPDATE] 
       @IDSETREGISTERID output
      ,@SEGMENTNAME 
      ,@SEGMENTDESCRIPTION
      ,@SEGMENTVIEW
      ,0
      ,@RECORDTYPEID
      ,@USEINQUERYDESIGNER
      ,1
      ,@NUMROWS
      ,@CHANGEAGENTID;

    --Mark as inactive to hide from searches

    --Make sure the RECORDTYPEID gets set.  The SP doesn't update it on an UPDATE.

    update dbo.[IDSETREGISTER] set
      [RECORDTYPEID] = @RECORDTYPEID,
      [ACTIVE] = (case when @NEWIDSET = 1 then 0 else [ACTIVE] end),
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @IDSETREGISTERID;

    --Save the ID set ID on the segment table...

    update dbo.[MKTSEGMENT] set
      [IDSETREGISTERID] = @IDSETREGISTERID,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE 
    where [ID] = @SEGMENTID;
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    set @RETVAL = 1;
  end catch


  if @LOCKRESULT is not null
    --Release the applock now that we are done with the segment

    exec dbo.[USP_MKTSEGMENT_RELEASEAPPLOCK] @SEGMENTID, 0;

  return isnull(@RETVAL, 0);