USP_QUERYVIEWCATALOG_CREATEORUPDATEMETADATA

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@QUERYVIEWSPEC	xml	IN
@QUERYVIEWSPECEXTENSION	xml	IN
@OUTPUTDEFINITIONXML	xml	IN
@DISPLAYNAME	nvarchar(255)	IN
@SUBGROUP	nvarchar(255)	IN
@PRIMARYKEYFIELD	nvarchar(128)	IN
@PRIMARYKEYTYPENAME	nvarchar(128)	IN
@ROOT	bit	IN
@OBJECTNAME	nvarchar(128)	IN
@OBJECTTYPE	tinyint	IN
@SECURITYUIFOLDER	nvarchar(255)	IN
@CHANGEAGENTID	uniqueidentifier	IN
@RECORDTYPE	nvarchar(50)	IN
@USEINREPORTMODELGENERATOR	bit	IN
@MARTKEY	nvarchar(255)	IN
@ALLOWATTRIBUTEEXTENSION	bit	IN
Definition

Copy


CREATE procedure [dbo].[USP_QUERYVIEWCATALOG_CREATEORUPDATEMETADATA]
    @ID [uniqueidentifier] = null output,
    @QUERYVIEWSPEC [xml] = null,
    @QUERYVIEWSPECEXTENSION [xml] = null,
    @OUTPUTDEFINITIONXML [xml] = null,
    @DISPLAYNAME [nvarchar](255),
    @SUBGROUP [nvarchar](255),
    @PRIMARYKEYFIELD [nvarchar](128),
    @PRIMARYKEYTYPENAME [nvarchar](128),
    @ROOT [bit],
    @OBJECTNAME [nvarchar](128),
    @OBJECTTYPE [tinyint] = 0,
    @SECURITYUIFOLDER [nvarchar](255),
    @CHANGEAGENTID [uniqueidentifier] = null,
    @RECORDTYPE [nvarchar](50)='',
    @USEINREPORTMODELGENERATOR [bit] = 0,
    @MARTKEY [nvarchar](255) = null,
    @ALLOWATTRIBUTEEXTENSION [bit] = 0

with execute as caller
as
set nocount on

declare @ExistingID uniqueidentifier
declare @TYPEID uniqueidentifier

declare @DBOBJECT nvarchar(128)

if @MARTKEY = ''
    set @MARTKEY = null

if @OBJECTTYPE = 0 
begin
    -- validate view name

    select @DBOBJECT = [TABLE_NAME] from [INFORMATION_SCHEMA].[VIEWS] where [TABLE_NAME] = @OBJECTNAME
    if @DBOBJECT is null and @MARTKEY is null
    begin
        raiserror ('View ''%s'' does not exist', 16, 1, @OBJECTNAME)
        return 1;
    end
end
else
begin
    select @DBOBJECT = [ROUTINE_NAME] from [INFORMATION_SCHEMA].[ROUTINES] where (ROUTINE_SCHEMA = 'dbo') and (ROUTINE_NAME = @OBJECTNAME)
    if @DBOBJECT is null and @MARTKEY = ''
    begin
        raiserror ('Table-valued function ''%s'' does not exist', 16, 1, @OBJECTNAME)
        return 1
    end
end

if @CHANGEAGENTID is null
    exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output

if len(coalesce(@RECORDTYPE,'')) > 0
    --Add record type if it does not exist.        

    exec dbo.[USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME] @NAME=@RECORDTYPE,@CHANGEAGENTID=@CHANGEAGENTID,@RECORDTYPEID=@TYPEID output;


if @ID is null
    begin
      select @ExistingID = [ID] from dbo.[QUERYVIEWCATALOG] where [OBJECTNAME] = @OBJECTNAME and [OBJECTTYPE] = 0
      set @ID = @ExistingID
    end
else
    select @ExistingID = [ID] from dbo.[QUERYVIEWCATALOG] where [ID] = @ID





declare @EnforceRACSecurity bit;



--set @EnforceRACSecurity=0;

set @EnforceRACSecurity=        
 coalesce(
                @QUERYVIEWSPEC.value(
                                        '
                                        declare namespace bbfa="bb_appfx_queryview";
                                        /bbfa:QueryViewSpec[1]/@EnforceRecordAccessSecurity
                                        '
                                        ,'bit')
                ,0);



declare @description nvarchar(max);

set @description=
coalesce(
                @QUERYVIEWSPEC.value(
                                        '
                                        declare namespace bbfa="bb_appfx_queryview";
                                        /bbfa:QueryViewSpec[1]/@Description
                                        '
                                        ,'nvarchar(max)')
                ,'')


declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

if @ExistingID is null
    begin
        if @ID is null set @ID = NewID()

        insert into dbo.[QUERYVIEWCATALOG] ([ID], [OBJECTNAME], [OBJECTTYPE], [RECORDTYPEID], [DISPLAYNAME], [ROOTOBJECT], [SUBGROUP], [PRIMARYKEYFIELD], [PRIMARYKEYTYPENAME], [OUTPUTDEFINITIONXML], [QUERYVIEWSPEC], [QUERYVIEWSPECEXTENSION], [SECURITYUIFOLDER], [ADDEDBYID], [CHANGEDBYID], [ENFORCERECORDACCESSSECURITY],[DESCRIPTION], [USEINREPORTMODELGENERATOR], [MARTKEY], [ALLOWATTRIBUTEEXTENSION], [DATEADDED], [DATECHANGED]) 
        values (@ID, @OBJECTNAME, @OBJECTTYPE, @TYPEID, @DISPLAYNAME, @ROOT, @SUBGROUP, @PRIMARYKEYFIELD, @PRIMARYKEYTYPENAME, @OUTPUTDEFINITIONXML, @QUERYVIEWSPEC, @QUERYVIEWSPECEXTENSION, @SECURITYUIFOLDER, @CHANGEAGENTID, @CHANGEAGENTID,@EnforceRACSecurity,@description, @USEINREPORTMODELGENERATOR, @MARTKEY, @ALLOWATTRIBUTEEXTENSION, @CURRENTDATE, @CURRENTDATE)
    end
else
    update dbo.[QUERYVIEWCATALOG] 
  set [OBJECTNAME] = @OBJECTNAME,
          [QUERYVIEWSPEC] = @QUERYVIEWSPEC,
          [QUERYVIEWSPECEXTENSION] = @QUERYVIEWSPECEXTENSION,
          [OUTPUTDEFINITIONXML] = @OUTPUTDEFINITIONXML,
          [RECORDTYPEID] = @TYPEID, 
          [DISPLAYNAME] = @DISPLAYNAME,
          [ROOTOBJECT] = @ROOT,
          [SUBGROUP] = @SUBGROUP,
          [PRIMARYKEYFIELD] = @PRIMARYKEYFIELD,
          [PRIMARYKEYTYPENAME] = @PRIMARYKEYTYPENAME,
          [SECURITYUIFOLDER] = @SECURITYUIFOLDER,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [ENFORCERECORDACCESSSECURITY] = @EnforceRACSecurity,
          [DESCRIPTION] = @description,
          [USEINREPORTMODELGENERATOR] = @USEINREPORTMODELGENERATOR,
          [MARTKEY] = @MARTKEY,
          [ALLOWATTRIBUTEEXTENSION] = @ALLOWATTRIBUTEEXTENSION,
          [DATECHANGED] = @CURRENTDATE,
          [OBJECTTYPE] = @OBJECTTYPE
    where ID = @ExistingID;