USP_DATALIST_CMSUSERGROUPSANDUSERSNOTINGROUP

Returns the users and groups available to add to the specified group

Parameters

Parameter	Parameter Type	Mode	Description
@CLIENTROLEID	int	IN	Input parameter indicating the context ID for the data list.
@NAME	nvarchar(255)	IN	Name
@XMLDATA	xml	IN	XMLData
@NUMROWSTORETURN	int	IN	Number of Rows To Return
@USERSORGROUPS	tinyint	IN	Return users or groups

Definition

Copy



                    CREATE procedure dbo.USP_DATALIST_CMSUSERGROUPSANDUSERSNOTINGROUP(@CLIENTROLEID int, @NAME nvarchar(255), @XMLDATA xml, @NUMROWSTORETURN int, @USERSORGROUPS tinyint = 0)

                    as
                    set nocount on;

                    declare @IDSTOREMOVE table (ID uniqueidentifier)
                    insert into @IDSTOREMOVE
                    select
                    t.r.value('@ID', 'uniqueidentifier')
                    from @XMLDATA.nodes('/t/r') t(r)

                    select top (@NUMROWSTORETURN) *
                    from 
                        (select top (@NUMROWSTORETURN) CR.Name, CR.Guid
                        from dbo.ClientRoles CR
                        left outer join (select CHILDCLIENTROLEID from dbo.CLIENTROLECHILD where PARENTCLIENTROLEID = @CLIENTROLEID) CRC on CRC.CHILDCLIENTROLEID = CR.ID
                        left outer join dbo.UFN_CLIENTROLE_GETPARENTIDS(@CLIENTROLEID) PARENTS on PARENTS.ID = CR.ID
                        left outer join @IDSTOREMOVE ITR on ITR.ID = CR.GUID
                        where CRC.CHILDCLIENTROLEID is null and PARENTS.ID is null and ITR.ID is null and CR.ID != @CLIENTROLEID and CR.EveryoneRole = 0 and CR.Deleted = 0 and Name like '%' + @NAME + '%' and (@USERSORGROUPS = 0 or @USERSORGROUPS = 1) 
                        order by CR.Name
                        union all
                        select top (@NUMROWSTORETURN) CU.FirstName + ' ' + CU.LastName + ' (' + CU.UserName + ')', CU.GUID
                        from dbo.clientusers CU
                        left outer join (select * from dbo.userroles where clientrolesid = @CLIENTROLEID) UR on CU.ID = UR.CLIENTUSERSID
                        left outer join @IDSTOREMOVE ITR on ITR.ID = CU.Guid
                        where UR.ClientRolesID is null and ITR.ID is null
                        and CU.Deleted = 0 and CU.InternalUser = 0 and CU.IsSupervisor = 0 and (CU.FirstName + ' ' + CU.LastName + ' (' + CU.UserName + ')') like '%' + @NAME + '%' and (@USERSORGROUPS = 0 or @USERSORGROUPS = 2) 
            and CU.Active = 1
                        order by CU.Firstname, CU.LastName, CU.UserName
                        ) items                        
                    order by items.Name