USP_DATAFORMTEMPLATE_EDITLOAD_CODETABLEENTRYPERMISSION

The load procedure used by the edit dataform template "Code Table Entry Permission Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nchar	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CODETABLEENTRYPERMISSIONS	xml	INOUT	Code table entry permissions

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CODETABLEENTRYPERMISSION
                    (
                        @ID nchar(73),
                        @DATALOADED bit = 0 output,
                        @CODETABLEENTRYPERMISSIONS xml = null output
                    )
                    with execute as owner
                    as
                        set nocount on;

                        set @DATALOADED = 0;

                        declare @SYSTEMROLEID uniqueidentifier;
                        declare @CODETABLECATALOGID uniqueidentifier;
                        declare @DBTABLENAME nvarchar(100);

                        set @SYSTEMROLEID = cast(substring(@ID,1,36) as uniqueidentifier);
                        set @CODETABLECATALOGID = cast(substring(@ID,38,36) as uniqueidentifier);

                        select 
                            @DBTABLENAME = DBTABLENAME,
                            @DATALOADED = 1
                        from 
                            dbo.CODETABLECATALOG 
                        where 
                            ID = @CODETABLECATALOGID
                            and 1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
                                (
                                    CODETABLECATALOG.CODETABLESPECXML.query
                                        (
                                            'declare namespace common="bb_appfx_commontypes";
                                            /*/common:InstalledProductList'
                                        )
                                );

                        declare @SQL nvarchar(max);

                        set @SQL = 'select @CODETABLEENTRYPERMISSIONS = 
                                (select SYSTEMROLEPERM_CODETABLEENTRY.ID, @CODETABLEID as CODETABLECATALOGID, CT.ID as CODETABLEENTRYID, CT.DESCRIPTION as CODETABLEENTRYNAME, coalesce(SYSTEMROLEPERM_CODETABLEENTRY.ISDENIED, 0) as ISDENIED
                                from dbo.' + @DBTABLENAME + ' CT left join dbo.SYSTEMROLEPERM_CODETABLEENTRY on CT.ID = SYSTEMROLEPERM_CODETABLEENTRY.CODETABLEENTRYID and SYSTEMROLEPERM_CODETABLEENTRY.SYSTEMROLEID = @ROLEID
                                order by CT.DESCRIPTION
                                for xml raw(''ITEM''), type, elements, root(''CODETABLEENTRYPERMISSIONS''), BINARY BASE64)'

                        exec sp_executesql @SQL, N'@ROLEID uniqueidentifier,@CODETABLEID uniqueidentifier,@CODETABLEENTRYPERMISSIONS xml output', @ROLEID = @SYSTEMROLEID, @CODETABLEID = @CODETABLECATALOGID, @CODETABLEENTRYPERMISSIONS = @CODETABLEENTRYPERMISSIONS output;

                        return 0;