USP_DATAFORMTEMPLATE_EDITLOAD_FEATUREGROUPPERMS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_FEATUREGROUPPERMS(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @FEATUREGROUPLIST xml = null output,
    @FEATUREGROUPLIST_DELETED xml = null output,
    @FEATUREGROUPLIST_ALL xml = null output,
    @NAME nvarchar(255) = null output
)
as

set nocount on;

set @DATALOADED = 0;
set @TSLONG = 0;

select
    @DATALOADED = 1,
    @TSLONG = SR.TSLONG,
    @NAME = NAME
from dbo.SYSTEMROLE AS SR
where 
    SR.ID = @ID;

set @FEATUREGROUPLIST_ALL = (select 
        SYSTEMROLEPERM_FEATUREGROUP.ID,
        FEATUREGROUPCATALOG.ID FEATUREGROUPCATALOGID,
        FEATUREGROUPCATALOG.UINAME, 
        FEATUREGROUPCATALOG.DESCRIPTION,
        FEATUREGROUPCATALOG.SPECXML,
        SYSTEMROLEPERM_FEATUREGROUP.GRANTORDENY
    from dbo.FEATUREGROUPCATALOG
    left join dbo.SYSTEMROLEPERM_FEATUREGROUP on FEATUREGROUPCATALOG.ID = SYSTEMROLEPERM_FEATUREGROUP.FEATUREGROUPCATALOGID
        and SYSTEMROLEPERM_FEATUREGROUP.SYSTEMROLEID = @ID
    where 1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
        (
            FEATUREGROUPCATALOG.SPECXML.query
                (
                    'declare namespace common="bb_appfx_commontypes";
                    /*/common:InstalledProductList'
                )
        )
    for xml raw('ITEM'), type, elements, root('FEATUREGROUPLIST_ALL'), BINARY BASE64);

return 0;