USP_DATAFORMINSTANCE_GETMETADATA_FORSPUPDATE

CREATE procedure [dbo].[USP_DATAFORMINSTANCE_GETMETADATA_FORSPUPDATE]
    @ID uniqueidentifier    

as

    set nocount on

    declare @SaveProc nvarchar(128)
    declare @LoadProc nvarchar(128)
    declare @FormUIXML xml
    declare @Mode smallint
    declare @ContextParam nvarchar(128)
    declare @HasChangeAgent bit
    declare @HasTSLong bit
    declare @LoadHasAppUser bit
    declare @SaveHasAppUser bit
    declare @LoadHasID bit
    declare @Loaded bit
    declare @HasMetaTags bit = 0

    set @Loaded = 0;

  with xmlnamespaces('bb_appfx_commontypes' as c)
    select @Loaded = 1, @FormUIXML = DATAFORMINSTANCECATALOG.FORMUIXML, @Mode = DATAFORMTEMPLATECATALOG.MODE, 
        @LoadProc = coalesce(DATAFORMTEMPLATECATALOG.LOADPROCEDURE, ''), @SaveProc = coalesce(DATAFORMTEMPLATECATALOG.SAVEPROCEDURE, ''),
        @ContextParam = DATAFORMTEMPLATECATALOG.CONTEXTPARAMETERNAME,
        @HasMetaTags = DATAFORMTEMPLATECATALOG.TEMPLATESPECXML.exist('//c:MetaTags')
    from dbo.DATAFORMINSTANCECATALOG 
        inner join dbo.DATAFORMTEMPLATECATALOG on DATAFORMINSTANCECATALOG.DATAFORMTEMPLATECATALOGID = DATAFORMTEMPLATECATALOG.ID
    where DATAFORMINSTANCECATALOG.ID = @ID and DATAFORMTEMPLATECATALOG.IMPLEMENTATIONTYPE = 0;

    if @Loaded = 0
        return

    set @HasTSLong = 0
    set @HasChangeAgent = 0
    set @LoadHasAppUser = 0
    set @SaveHasAppUser = 0
    set @LoadHasID = 0

    if len(@LoadProc) > 0
        begin
            -- @TSLONG
            select @HasTSLong = 1 from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_SCHEMA='dbo' and SPECIFIC_NAME = @LoadProc and PARAMETER_NAME = '@TSLONG'

            -- @CURRENTAPPUSERID
            select @LoadHasAppUser = 1 from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_SCHEMA='dbo' and SPECIFIC_NAME = @LoadProc and PARAMETER_NAME = '@CURRENTAPPUSERID'

            -- @ID
            select @LoadHasID = 1 from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_SCHEMA='dbo' and SPECIFIC_NAME = @LoadProc and PARAMETER_NAME = '@ID'
        end

    if len(@SaveProc) > 0 
        begin
            -- @CHANGEAGENTID
            select @HasChangeAgent = 1 from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_SCHEMA='dbo' and SPECIFIC_NAME = @SaveProc and PARAMETER_NAME = '@CHANGEAGENTID'

            -- @CURRENTAPPUSERID
            select @SaveHasAppUser = 1 from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_SCHEMA='dbo' and SPECIFIC_NAME = @SaveProc and PARAMETER_NAME = '@CURRENTAPPUSERID'
        end        

    select    
        @LoadProc 'LOADPROCEDURE',
            @SaveProc 'SAVEPROCEDURE', 
            @FormUIXML 'FORMUIXML',
            @Mode 'MODE',
            @ContextParam 'CONTEXTPARAMETERNAME',
            @HasChangeAgent 'HASCHANGEAGENT',
            @HasTSLong 'HASTSLONG',
            @LoadHasAppUser 'LOADHASAPPUSER',
            @SaveHasAppUser 'SAVEHASAPPUSER', 
            @LoadHasID 'LOADHASID',
            @HasMetaTags 'HASMETATAGS'