USP_MKTCONSOLIDATEDQUERYVIEW_CREATEVIEW

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

  begin try
    --Drop the view if it exists...

    if exists(select 1 from [INFORMATION_SCHEMA].[VIEWS] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @VIEWNAME)
      exec ('drop view dbo.[' + @VIEWNAME + ']');

    --Create the consolidated query view...

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

    --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;