USP_MKTCONSOLIDATEDQUERYVIEW_CREATEORUPDATEVIEW

create procedure dbo.[USP_MKTCONSOLIDATEDQUERYVIEW_CREATEORUPDATEVIEW]
(
  @VIEWNAME nvarchar(128),
  @VIEWSQL nvarchar(max)
)
as
  set nocount on;

  declare @CREATEVIEWSQL nvarchar(max);

  begin try
    --Create or alter the consolidated query view...

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

    set @CREATEVIEWSQL = @CREATEVIEWSQL + ' view dbo.[' + @VIEWNAME + ']' + char(13) +
                         'as' + char(13) + @VIEWSQL;
    exec (@CREATEVIEWSQL);

    --Grant select rights to 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;