USP_DATALIST_CMSUSERGROUPSANDUSERS

Returns a list of all CMS Users and User Groups

Parameters

Parameter	Parameter Type	Mode	Description
@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_CMSUSERGROUPSANDUSERS(@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 @IDSTOREMOVE ITR on ITR.ID = CR.GUID
                        where ITR.ID is null and CR.EveryoneRole = 0 and CR.Deleted = 0 and Name like '%' + @NAME + '%' and (@USERSORGROUPS = 0 or @USERSORGROUPS = 1) 
                        union all
                        select top (@NUMROWSTORETURN) CU.FirstName + ' ' + CU.LastName + ' (' + CU.UserName + ')', CU.GUID
                        from dbo.clientusers CU
                        left outer join @IDSTOREMOVE ITR on ITR.ID = CU.Guid
                        where ITR.ID is null and CU.IsSupervisor = 0 and CU.InternalUser = 0 and CU.Deleted = 0 and CU.Active = 1
                        and (CU.FirstName + ' ' + CU.LastName + ' (' + CU.UserName + ')') like '%' + @NAME + '%' and (@USERSORGROUPS = 0 or @USERSORGROUPS = 2) 
            ) items
                    order by items.Name