USP_QUERY_CREATEORUPDATEVIEW

create procedure [dbo].[USP_QUERY_CREATEORUPDATEVIEW]
    @QUERYID uniqueidentifier,
    @SQL [nvarchar](max)

with execute as owner
as

set nocount on

declare @FormattedQueryID nvarchar(36)
declare @ViewName as nvarchar(128)
declare @CreateViewSQL as nvarchar(max)
declare @OBJID as integer

set @FormattedQueryID = replace(cast(@QUERYID as nvarchar(36)), '-', '_')
set @ViewName = 'V_QUERY_' + @FormattedQueryID

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 @CreateViewSQL = 'alter VIEW [' + @ViewName + ']'
else
    set @CreateViewSQL = 'create VIEW [' + @ViewName + ']'

set @CreateViewSQL = @CreateViewSQL + ' as
    ' + @SQL

-- execute the sql to create or alter the view 
exec (@CreateViewSQL)

if @@error <> 0 return 1;

-- grant rights for new views
if @OBJID is null
    exec ('grant select on [' + @ViewName + '] to BBAPPFXSERVICEROLE')

return 0;