USP_MKTSEGMENTLIST_CREATEIDSETVIEW

CREATE procedure dbo.[USP_MKTSEGMENTLIST_CREATEIDSETVIEW]
(
  @SEGMENTLISTID uniqueidentifier,
  @ISDUPLICATE bit
)
with execute as owner
as
  set nocount on;

  declare @VIEWNAME nvarchar(128);
  declare @SQL nvarchar(max);
  declare @MINIMUMDATAID uniqueidentifier;
  declare @MAXIMUMDATAID uniqueidentifier;

  begin try
    select
      @MINIMUMDATAID = [MINIMUMDATAID],
      @MAXIMUMDATAID = [MAXIMUMDATAID],
      @VIEWNAME = dbo.[UFN_MKTSEGMENTLIST_MAKEVIEWNAME_FORIDSET]([ID], @ISDUPLICATE),
      @SQL = ''
    from dbo.[MKTSEGMENTLIST]
    where [ID] = @SEGMENTLISTID;

    if @MINIMUMDATAID is null or @MAXIMUMDATAID is null
      --This is for backwards compatibility for lists that don't have sequential data IDs.  This sql comment is 

      --important because USP_MKTSEGMENTLIST_DELETEIMPORTDATA looks for this comment for upgraded lists.

      set @SQL = '/*##LIST UPGRADE## - DO NOT REMOVE OR MODIFY*/' + char(13);

    if exists(select 1 from INFORMATION_SCHEMA.VIEWS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @VIEWNAME)
      set @SQL = @SQL + 'alter';
    else
      set @SQL = @SQL + 'create';

    --Create the view...

    set @SQL = @SQL + ' view dbo.[' + @VIEWNAME + ']' + char(13) +
               'as' + char(13) +
               '  select [ID]' + char(13) +
               '  from dbo.[MKTSEGMENTLISTDATA]' + char(13);

    if @MINIMUMDATAID is not null and @MAXIMUMDATAID is not null
      --Always use the clustered index when querying this table because the IDs are sequential and this will be faster...

      set @SQL = @SQL + '  where [ID] between ''' + cast(@MINIMUMDATAID as nvarchar(36)) + ''' and ''' + cast(@MAXIMUMDATAID as nvarchar(36)) + '''' + char(13);
    else
      --This is for backwards compatibility for lists that don't have sequential data IDs...

      set @SQL = @SQL + '  where [SEGMENTLISTID] = ''' + cast(@SEGMENTLISTID as nvarchar(36)) + '''' + char(13);

    set @SQL = @SQL + '  and [ISDUPLICATE] = ' + cast(@ISDUPLICATE as nvarchar(1));

    exec (@SQL);

    --Grant select rights on the view...

    exec ('grant select on dbo.[' + @VIEWNAME + '] to BBAPPFXSERVICEROLE');
  end try

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

  return 0;