USP_BUSINESSPROCESS_CREATEORUPDATETEMPVIEW

create procedure dbo.USP_BUSINESSPROCESS_CREATEORUPDATETEMPVIEW
(
  @VIEWNAME nvarchar(128),
  @SELECTSQL nvarchar(max)
)
with execute as owner
as begin

    declare @SQL nvarchar(max);
    declare @OBJID int;

    set nocount on;

    select @OBJID = id from dbo.sysobjects where id = object_id(N'[dbo].[' + @VIEWNAME + ' ]') and type in (N'V');

    if not @OBJID is null
        -- view already exists so use alter

        set @SQL = 'alter view dbo.[';
    else
        set @SQL = 'create view dbo.[';

    set @SQL = @SQL + @VIEWNAME + '] as' + char(13) + @SELECTSQL + ';';

    exec (@SQL);

    if @@error <> 0 return 1;

    -- grant rights for new views

    set @SQL = 'grant select on dbo.[' + @VIEWNAME + '] to BBAPPFXSERVICEROLE';
    exec (@SQL);

    return 0;

end