
/****** Object:  StoredProcedure [mdm].[udpMembersGet]    Script Date: 10/30/2011 14:19:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [mdm].[udpMembersGet]  
@User_ID INT, @Version_ID INT, @Hierarchy_ID INT, @Entity_ID INT, @Parent_ID INT=NULL, @Member_ID INT=NULL, @MemberType_ID TINYINT, @Attribute_ID INT=NULL, @AttributeValue INT=NULL, @PageNumber INT=NULL, @PageSize INT=NULL, @SortColumn [sysname]=NULL, @SortDirection NVARCHAR (4)=NULL, @SearchTable [mdm].[MemberGetCriteria] READONLY, @AttributeGroup_ID INT=NULL, @CountOnly BIT=0, @IDOnly BIT=0, @ColumnString NVARCHAR (MAX)=NULL, @MemberCount INT=NULL OUTPUT  
WITH EXECUTE AS N'mds_schema_user'  
AS  
BEGIN  
    SET NOCOUNT ON;  
      
    DECLARE @SQL                NVARCHAR(MAX),  
            @ViewName           NVARCHAR(262),  
            @ViewNameRaw        NVARCHAR(262),  
            @TableName          NVARCHAR(262),  
            @SecurityTable      NVARCHAR(262),  
            @strPageSize        NVARCHAR(30),  
            @StartRow           INT,  
            @strStartRow        NVARCHAR(30),  
            @strVersion_ID        NVARCHAR(30),  
            @strEntity_ID        NVARCHAR(30),  
            @strUser_ID            NVARCHAR(30),  
            @strMember_ID        NVARCHAR(30),  
            @strMemberType_ID    NVARCHAR(30),  
            @strHierarchy_ID    NVARCHAR(30),  
            @strParent_ID        NVARCHAR(30),  
            @strPrvID            NVARCHAR(30),  
            @Model_ID            INT,  
            @Privilege_ID        INT,  
            @Securable_ID        INT,  
            @Object_ID            INT,  
            @RootSecured        BIT,  
            @Mode                INT,  
            @UseMemberSecurity    INT, --0=No,1=Yes,2=LeafOnly   
            @SortOrderColumn    sysname,  
            @SortOrderColumnQuoted	NVARCHAR(300),  
            @SortColumnQuoted	NVARCHAR(300),  
            @SearchTerm            NVARCHAR(max),  
            @MemberSecurityCTE    NVARCHAR(MAX);  
      
    DECLARE @e sysname;   
    SET @e = OBJECT_NAME(@@PROCID);  
  
    SELECT @Model_ID = e.Model_ID  
    FROM mdm.tblEntity AS e INNER JOIN mdm.tblModelVersion AS mv ON e.Model_ID = mv.Model_ID  
    WHERE e.ID = @Entity_ID AND mv.ID = @Version_ID;  
      
    SET @PageSize = COALESCE(@PageSize, (SELECT SettingValue FROM mdm.tblSystemSetting WHERE SettingName = CAST(N'RowsPerBatch' AS NVARCHAR(100))))      
  
    -- ensure @PageNumber is >= 1.  
    IF COALESCE(@PageNumber, 0) < 1 SET @PageNumber = 1;        
              
    --Initialize variables  
    SELECT  
         @SQL = CAST(N'' AS NVARCHAR(max))  
        ,@ColumnString        = NULLIF(@ColumnString, N'')  
        ,@SortColumn        = NULLIF(NULLIF(@SortColumn, N''), N'ID') -- already sorting by ID after the specified sort column  
        ,@SortDirection        = ISNULL(NULLIF(UPPER(@SortDirection), N''), N'ASC')  
        ,@strVersion_ID        = CONVERT(NVARCHAR(30), @Version_ID)  
        ,@strEntity_ID        = CONVERT(NVARCHAR(30), @Entity_ID)  
        ,@strUser_ID        = CONVERT(NVARCHAR(30), @User_ID)  
        ,@strMember_ID        = CONVERT(NVARCHAR(30), @Member_ID)  
        ,@strMemberType_ID    = CONVERT(NVARCHAR(30), @MemberType_ID)  
        ,@strHierarchy_ID    = CONVERT(NVARCHAR(30), @Hierarchy_ID)  
        ,@strParent_ID        = CONVERT(NVARCHAR(30), @Parent_ID)  
        ,@UseMemberSecurity = 0;  
         
    --Initialize dependant variables  
    SELECT  
         @SortOrderColumn    = @SortColumn  
        ,@StartRow            = (@PageNumber - 1) * (@PageSize) + 1  
        ,@strPageSize        = CONVERT(NVARCHAR(30), @PageSize);  
    SET @strStartRow        = CONVERT(NVARCHAR(30), @StartRow);  
  
    --Test for invalid parameters  
    IF (@Model_ID IS NULL)   
        OR (UPPER(@SortDirection) NOT IN (N'ASC', N'DESC')) --Invalid @SortDirection  
    BEGIN  
        --On error, return NULL results  
        RAISERROR ('%s: Invalid Parameters.', 11, 1, @e);  
        RETURN(1);  
    END; --if  
      
  
    --Silly, but no way to inject with this.  
    SET @SortDirection = CASE WHEN UPPER(@SortDirection) = N'ASC' THEN N'ASC' ELSE N'DESC' END  
      
    /*  
    convert @SearchTerm into something that is injection safe.  
    */  
    IF EXISTS (SELECT 1 FROM @SearchTable)  
    BEGIN  
        DECLARE @maxFilterCount		INT,  
                @counter			INT,  
                @groupID			INT,  
                @schemaName			sysname,  
                @objectName			sysname,  
                @fullObjectName		NVARCHAR(MAX),  
                @operator			NVARCHAR(15),  
                @operatorParameters	NVARCHAR(MAX),  
                @filterValue		NVARCHAR(MAX),  
                @filterClause		NVARCHAR(MAX),  
                @currentGroup		INT,  
                @matchOrderBy		NVARCHAR(MAX),  
                @regexMask			INT = mdq.RegexMask(1, 1, 1, 0, 0, 0, 1); --CultureInvariant, ExplicitCapture, IgnoreCase, SingleLine (71)  
                  
        SET @currentGroup = 0;  
        SET @SearchTerm = N'';  
        SELECT @counter = MIN(ID), @maxFilterCount = COUNT(1) FROM @SearchTable;  
          
        --Build filter clause using table param for joins  
        WHILE @counter <= @maxFilterCount  
        BEGIN  
            SET @filterClause = NULL;      
            SELECT   
                 @operator = UPPER(LTRIM(RTRIM(COALESCE(Operator, N''))))  
                ,@filterValue = [Value]  
                ,@groupID = GroupId  
                ,@schemaName = LTRIM(RTRIM(COALESCE(SchemaName, N'')))  
                ,@objectName = LTRIM(RTRIM(COALESCE(ObjectName, N'')))  
                ,@operatorParameters = UPPER(LTRIM(RTRIM(COALESCE(OperatorParameters, N''))))  
            FROM @SearchTable WHERE ID = @counter;  
              
            SET @fullObjectName = CASE WHEN LEN(@schemaName) > 0 THEN QUOTENAME(@schemaName) + N'.' ELSE N'' END + QUOTENAME(@objectName);  
            DECLARE @isOperatorNegated  BIT = mdq.RegexIsMatch(@operator, N'NOT ', @regexMask),  
                    @isOperatorFuzzy    BIT = mdq.RegexIsMatch(@operator, N'^(NOT )?MATCH$', @regexMask),  
                    @isOperatorRegex    BIT = mdq.RegexIsMatch(@operator, N'^(NOT )?REGEX$', @regexMask),  
                    @isOperatorIsNull   BIT = mdq.RegexIsMatch(@operator, N'^IS (NOT )?NULL$', @regexMask),  
                    @isOperatorIn       BIT = mdq.RegexIsMatch(@operator, N'^(NOT )?IN$', @regexMask);  
            DECLARE @isOperatorValid    BIT = CASE WHEN @operator IN (  
                             N'='  
                            ,N'<>'   
                            ,N'LIKE'  
                            ,N'NOT LIKE'  
                            ,N'>'  
                            ,N'<'  
                            ,N'>='  
                            ,N'<=')   
                            OR @isOperatorFuzzy = 1   
                            OR @isOperatorRegex = 1   
                            OR @isOperatorIsNull = 1  
                            OR @isOperatorIn = 1  
                            THEN 1 ELSE 0 END;  
  
            IF (@isOperatorValid = 1   
                AND LEN(@fullObjectName) > 0   
                AND (@currentGroup <> @groupID OR @groupID = 0))  
            BEGIN  
                SET @currentGroup = @groupID;                  
                DECLARE @getValueSql NVARCHAR(MAX) = N' (SELECT [Value] FROM @SearchTable WHERE ID = ' + CONVERT(NVARCHAR(100), @counter) + N')';  
                  
                IF (@isOperatorFuzzy = 1)  
                BEGIN   
                      
                    -- do fuzzy match  
                    /*  
                     * Fuzzy matching requires operator parameters in this format: 'minSimilarity[ algorithm][ containmentBias]'  
                     *   
                     * minSimilarity: 0 <= value <= 1. Used by all algorithms.  
                     * algorithm:    
                     *      - 0 = Levenstein (default)   
                     *      - 1 = Jaccard  
                     *      - 2 = JaroWinkler  
                     *      - 3 = Longest Common Subsequence  
                     * lengthThreshold 0 <= value <= 1. Default = 0.62. Used only by Jaccard and Longest Common Subsequence algorithms.   
                     *   
                     * Example: '0.5 1 0.32' means use Jaccard algorithm with minSimilarity = 0.5 and containmentBias = 0.32  
                     */  
                    DECLARE @Levenstein  NVARCHAR(1)              = N'0',  
                            @Jaccard     NVARCHAR(1)              = N'1',  
                            @JaroWinkler NVARCHAR(1)              = N'2',  
                            @LongestCommonSubsequence NVARCHAR(1) = N'3',  
                            @numericPattern NVARCHAR(100) = N'(\d*(\.\d+)?)';  
                    DECLARE @pattern NVARCHAR(100) = N'^(?<minSimilarity>' + @numericPattern + N')( +(?<algorithm>(\d)))?( +(?<containmentBias>' + @numericPattern + N'))?',  
                            @defaultAlgorithm NVARCHAR(1) = @Levenstein,  
                            @defaultLengthThreshold NVARCHAR(10) = N'0.62';  
                    DECLARE @minSimilarity NVARCHAR(100) = mdq.RegexExtract(@operatorParameters, @pattern, N'minSimilarity', @regexMask),  
                            @algorithm NVARCHAR(1) = COALESCE(NULLIF(mdq.RegexExtract(@operatorParameters, @pattern, N'algorithm', @regexMask), N''), @defaultAlgorithm),                              
                            @lengthThreshold NVARCHAR(100) = COALESCE(NULLIF(mdq.RegexExtract(@operatorParameters, @pattern, N'containmentBias', @regexMask), N''), @defaultLengthThreshold);  
                              
                    -- validate parameters  
                    IF (ISNUMERIC(@minSimilarity) = 0)     
                    BEGIN   
                	    RAISERROR 100032 N'A valid similarity value was not provided for the fuzzy match operation.';  
                        RETURN;  
                    END  
                    IF (@algorithm NOT IN (@Levenstein, @Jaccard, @JaroWinkler, @LongestCommonSubsequence))   
                    BEGIN   
                	    RAISERROR 100033 N'An invalid fuzzy match algorithm code was specified.';  
                        RETURN;  
                    END  
                    IF (ISNUMERIC(@lengthThreshold) = 0)   
                    BEGIN   
                	    RAISERROR 100034 N'An invalid length threshold value was specified.';  
                        RETURN;  
                    END  
  
                    SET @filterClause = N'mdq.Similarity(' + @fullObjectName   
						+ N', ' + @getValueSql   
						+ N', ' + @algorithm      
						+ N', ' + @lengthThreshold  
						+ N', '   
						+ CASE @isOperatorNegated   
								WHEN 0 THEN @minSimilarity  
								ELSE N'0.0'   
						END --case  
						+ N')';  
                          
                    --Add the match expression to the custom ORDER BY clause  
					SET @matchOrderBy =   
						CASE  
							WHEN @matchOrderBy IS NULL THEN N''  
							ELSE @matchOrderBy + N' + '  
						END   
						+ N'  
						'   
						+ CASE @isOperatorNegated  
							WHEN 0 THEN @filterClause  
							WHEN 1 THEN N'(1.0 - ' + @filterClause + N')'  
						END; --case  
	                      
                    --Complete the rest of the predicate  
                    SET @filterClause =   
						N'('   
						+ @filterClause   
						+ CASE @isOperatorNegated  
							WHEN 0 THEN N' >= '  
							WHEN 1 THEN N' < '  
                        END --case  
                        + @minSimilarity   
                        + N')';  
						  
                END ELSE IF (@isOperatorRegex = 1)  
                BEGIN  
                      
                    -- do regular expression match  
                      
                    -- This if statement is commented out because, for now, the desired behavior for an invalid  
                    -- regular expression is to allow an exception to be thrown when it is parsed.   
                    --IF (mdq.RegexIsValid(@filterValue) = 1)   
                    --BEGIN  
                    SET @filterClause = N'(mdq.RegexIsMatch(' + @fullObjectName + N', ' + @getValueSql + N', ' + CONVERT(NVARCHAR(MAX), @regexMask) + N') '  
                    + CASE @isOperatorNegated  
                        WHEN 0 THEN N'='  
                        WHEN 1 THEN N'<>'  
                        END  
                    + N' 1)';  
                    --END  
                END ELSE   
                BEGIN   
                      
                    -- do other match                  
                    SET @filterClause =                   
                        @fullObjectName + N' '   
                        + @operator  
                        + CASE   
                            WHEN @isOperatorIsNull = 1   
                                THEN N''  
                            WHEN @isOperatorIn = 1  
                                THEN N' (SELECT [Value] FROM @SearchTable WHERE GroupId = ' + CONVERT(NVARCHAR(100), @groupID) + N')'  
                            ELSE @getValueSql  
                        END; --case  
                END  
                                  
                IF @filterClause IS NOT NULL   
                BEGIN  
                    IF LEN(@SearchTerm) <> 0   
                    BEGIN  
                        SET @SearchTerm = @SearchTerm + N'  
                            AND ';  
                    END; --if  
                    SET @SearchTerm = @SearchTerm + @filterClause;  
                END; --if filterclause is not null  
            END; --if  
                  
            SET @counter += 1;  
        END;    --WHILE @counter <= @maxFilterCount  
    END; --EXISTS in @SearchTerm  
    SET @SearchTerm = NULLIF(@SearchTerm, N'');  
      
    --PRINT @SearchTerm;  
      
    --IF EXISTS(SELECT 1 FROM mdm.tblAttribute WHERE Entity_ID = @Entity_ID AND MemberType_ID = @MemberType_ID AND [Name] = @SortColumn AND DomainEntity_ID > 0) BEGIN --DBA Sort Column  
    --    IF @DisplayType_ID = 1 SET @SortOrderColumn = quotename(@SortOrderColumn);  
    --    ELSE IF @DisplayType_ID = 2 SET @SortOrderColumn = quotename(@SortColumn + N'.Code') + N' ';  
    --    ELSE IF @DisplayType_ID = 3 SET @SortOrderColumn = quotename(@SortColumn + N'.Name') + N' ' +   
    --        @SortDirection + N', ' + quotename(@SortColumn + N'.Code');  
    --END ELSE  
      
      
    SET @SortOrderColumnQuoted = QUOTENAME(@SortOrderColumn);  
    SET @SortColumnQuoted = QUOTENAME(@SortColumn);  
      
    --Assign the Object ID {** Need to refactor (consolidate lists, security types, and enumerations **}  
    IF @Parent_ID IS NOT NULL AND @Hierarchy_ID IS NOT NULL BEGIN --an Explicit Hierarchy  
        SET @Object_ID = 6;  
        SET @Securable_ID = @Hierarchy_ID;  
    END ELSE BEGIN   
        SET @Object_ID = 3;  
        SET @Securable_ID = @Entity_ID;  
    END; --if  
  
     --Fetch the default privilege for the selected member type within the current entity.  
    --Due to bi-directional inheritance of privileges in model security, if a 99 is returned  
    --this should be interpreted as a soft deny (evaluating the member type permission requires an eval of attribute permissions)  
    --(i.e. if the user does NOT have any permissions at the DH levels evaluated below, 99 == DENY)  
    --PERF - moved this call to a udp to decrease the execution plan compile time.    
    EXEC mdm.udpSecurityUserMemberDefault   
		@User_ID = @User_ID,   
		@Item_ID = @Securable_ID,   
		@Object_ID = @Object_ID,   
		@MemberType_ID = @MemberType_ID,   
		@Privilege_ID = @Privilege_ID OUTPUT;  
		  
    --Incorporate Derived Hierarchy explicit assignments (EDM-2384)  
    IF @Privilege_ID = (SELECT ID FROM mdm.tblSecurityPrivilege WHERE Code = CAST(N'DENY' AS NVARCHAR(15)))  
        --If default privilege is Deny then do not return any rows  
        SELECT @PageSize = 0, @strPageSize = CAST(N'0' AS NVARCHAR(30)); --!I think this code will cause "ROWCOUNT:=0" which means return *all* rows, so it won't work as expected?  
    ELSE      
        --If the object appears in a Derived Hierarchy then resolve against any explicit assignments for the hierarchy(ies)  
        SET @Privilege_ID = mdm.udfMin(mdm.udfSecurityUserHierarchyDerivedItem(@User_ID, @Hierarchy_ID, @Object_ID, @Securable_ID), @Privilege_ID);  
  
    --Identify data sources  
    SELECT   
        @ViewName = N'mdm.' + quotename(mdm.udfViewNameGetByID(@Entity_ID, @MemberType_ID, 4)), --EXP View - Which is exploaded by hierarchy parent  
        @ViewNameRaw = N'mdm.' + quotename(mdm.udfViewNameGetByID(@Entity_ID, @MemberType_ID, 0)),  
        @TableName = N'mdm.' + quotename(mdm.udfTableNameGetByID(@Entity_ID, 4)),  
        @SecurityTable = N'mdm.' + quotename(mdm.udfTableNameGetByID(@Entity_ID, 6));  
      
    --Extra perf - Needs more testing  
    If @SortColumnQuoted IN (N'[Code]', N'[Name]') AND NULLIF(@SearchTerm,N'') IS NULL SET @ViewNameRaw  = N'(SELECT * FROM mdm.' + quotename(mdm.udfTableNameGetByID(@Entity_ID, @MemberType_ID)) + ' WHERE Status_ID =1) ';  
  
      
    --These cte's are selectively added to the overall query based on if member security is needed to be applied   
    SET @MemberSecurityCTE = '  
                            --Member security cte  
                            --TODO: Verify that the user to group(s) resolution is correct  
                            WITH ms as  
                            (  
                                SELECT  
                                    Version_ID,  
                                    ' + CASE WHEN @MemberType_ID =1 THEN + ' EN_ID' ELSE + 'HP_ID' END + N'     AS Member_ID,  
                                    MemberType_ID,  
                                    Privilege_ID,  
                                    SecurityRole_ID  
                                FROM  
                                    ' + @SecurityTable + ' X  
                                INNER JOIN [mdm].[viw_SYSTEM_SECURITY_USER_ROLE] U ON X.SecurityRole_ID = U.Role_ID   
                                    AND U.User_ID = ' + @strUser_ID + '  
                            ),  
                            msr as   
                            (  
                                SELECT ROW_NUMBER() OVER (PARTITION BY Version_ID,Member_ID,MemberType_ID ORDER BY Privilege_ID,SecurityRole_ID ASC) AS RN , * FROM ms  
                            ),  
                            membersresolved as  
                            (  
                            SELECT   
                                *   
                            FROM   
                                msr X   
                                  
                                WHERE X.RN = 1  
                            )';  
  
    --Fetch the list of columns  
    IF @IDOnly = 1 SET @ColumnString = CAST(N'T.ID' AS NVARCHAR(max));  
    ELSE IF @CountOnly = 0 BEGIN  
        IF @ColumnString IS NULL  
            EXEC mdm.udpAttributeColumnListGet @User_ID, @Entity_ID, @MemberType_ID, @AttributeGroup_ID, @ColumnString OUTPUT;  
    END ELSE SET @ColumnString = CAST(N'@MemberCount = COUNT(T.ID)' AS NVARCHAR(max));  
      
    -------------------------------------------------------------------------------------  
    --Figure out if Member security is used  
    SET @RootSecured = 0;  
    --PRINT '@UseMemberSecurity: ' + convert(nvarchar(100),@UseMemberSecurity)  
    IF @MemberType_ID IN (1,2) SET @UseMemberSecurity = mdm.udfUseMemberSecurity(@User_ID, @Version_ID, 1, @Hierarchy_ID, NULL, @Entity_ID, @MemberType_ID, @Attribute_ID);  
    IF (@UseMemberSecurity = 1) BEGIN      
        --Check to see if Root is secured for Explicit  
        IF (@Hierarchy_ID IS NOT NULL AND @Attribute_ID IS NULL AND EXISTS(  
            SELECT 1 FROM mdm.[viw_SYSTEM_SECURITY_USER_MEMBER] WHERE  
                IsMapped = 1 AND   
                User_ID = @User_ID AND  
                Hierarchy_ID = @Hierarchy_ID AND  
                HierarchyType_ID = 0 AND  
                Member_ID = 0  
            ))  
        --Check to see if Root is secured for Derived Hierarchy  
        OR (EXISTS(  
            SELECT 1 FROM mdm.[viw_SYSTEM_SECURITY_USER_MEMBER] WHERE   
                IsMapped = 1 AND   
                User_ID = @User_ID AND  
                Hierarchy_ID IN (SELECT Hierarchy_ID FROM mdm.[viw_SYSTEM_SCHEMA_HIERARCHY_DERIVED_LEVELS] WHERE Foreign_ID = @Entity_ID AND [Object_ID] = 3) AND  
                HierarchyType_ID = 1 AND  
                Member_ID = 0  
            ))  
            SET @RootSecured = 1;  
    END; --if  
  
    ------------------------------------------------------------------------------  
    --When EH in DH there is a bug: UseMemberSecurity   
    --Leafs should be 2 and Consolidations should to be 1  
  
    SET @strPrvID = CAST(@Privilege_ID AS NVARCHAR(30));    
      
    --in the case of no member security, a Privilege_ID of 99 here  
    --should be interpreted as a DENY because the user had no  
    --inherited privileges to the memberType, attribute, attribute group or DH  
    IF @UseMemberSecurity = 0 AND @Privilege_ID = 99   
    BEGIN  
	    SET @Privilege_ID = 1;  
	    SET @strPrvID = CAST(@Privilege_ID AS NVARCHAR(30));  
    END;  
    	  
    IF @CountOnly = 0   
    BEGIN  
        IF @UseMemberSecurity = 0  
            SET @ColumnString = @ColumnString + N', ' + @strPrvID + N' AS Privilege_ID';   
        ELSE  
            SET @ColumnString = @ColumnString + N', CONVERT(INTEGER,ISNULL(SR.Privilege_ID, ' + @strPrvID + N')) AS Privilege_ID';  
    END;--if   
      
    --PRINT '@UseMemberSecurity: ' + convert(nvarchar(100),@UseMemberSecurity)  
      
    --Criterion 1: display all members (Attribute Explorer)  
    --First part is normal AE and the second is the root of a DH  
    IF (@Hierarchy_ID IS NULL AND @Attribute_ID IS NULL) OR (@Hierarchy_ID IS NOT NULL AND @Parent_ID IS NULL AND @Attribute_ID IS NULL) BEGIN  
  
        IF @UseMemberSecurity <> 0 AND @MemberType_ID = 1 BEGIN  
  
            SET @SQL = @SQL + N'   
            INNER JOIN membersresolved AS SR  
                ON SR.Member_ID = T.ID AND SR.Version_ID = T.Version_ID';  
  
        END ELSE IF @UseMemberSecurity = 1 AND @MemberType_ID = 2 BEGIN  
  
            SET @SQL = @SQL + N'  
            LEFT JOIN membersresolved AS SR  
                ON SR.Member_ID = T.ID AND SR.Version_ID = T.Version_ID';  
  
        END; --if  
  
        SET @SQL = @SQL + N'  
            WHERE T.Version_ID = ' + @strVersion_ID;  
              
        ---- Bug 358879 - given no member security filtering, must exclude items on deny.  
        IF @UseMemberSecurity = 0 BEGIN  
            SET @SQL = @SQL + N'  
                AND ' + @strPrvID + ' <> 1';  
        END;  
  
		IF @Member_ID IS NOT NULL BEGIN    
			-- Only get the specified member.  
			SET @SQL += N'   
				AND T.ID = ' + @strMember_ID;   
		END  
  
        IF @UseMemberSecurity = 1 AND @MemberType_ID = 2 SET @SQL = @SQL + N'  
                AND ISNULL(SR.Privilege_ID, 0) <> CASE   
                    WHEN T.Hierarchy_ID IN (SELECT DISTINCT Hierarchy_ID FROM mdm.[viw_SYSTEM_SECURITY_USER_MEMBER] WHERE User_ID = ' + @strUser_ID + N' AND Entity_ID = ' + @strEntity_ID + N' AND HierarchyType_ID = 0) THEN 0  
                    ELSE -1  
                END';  
  
        IF @SearchTerm IS NOT NULL SET @SQL = @SQL + N'  
                AND ' + @SearchTerm;  
  
        IF @CountOnly = 0 BEGIN  
  
            --First populate temporary table with IDs of rows  
            IF @PageSize <> 0 BEGIN  
                SET @SQL = N'  
                --Get keyset up to end of requested page              
                DECLARE @TempItems TABLE(RowNo INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, ID INT, Privilege_ID INT);  
                SET ROWCOUNT ' + CONVERT(NVARCHAR(30), CASE @PageSize WHEN 0 THEN 0 ELSE @StartRow + @PageSize - 1 END) + N';' +  
                CASE WHEN @UseMemberSecurity <> 0 THEN + @MemberSecurityCTE ELSE + ' ' END + N'              
                INSERT INTO @TempItems(ID, Privilege_ID)  
                SELECT T.ID, ' + CASE WHEN @UseMemberSecurity <> 0 THEN N'SR.Privilege_ID' ELSE N'NULL' END + N'  
                FROM ' + @ViewNameRaw + N' AS T' + @SQL;  
  
                --if using member security, replace NULLs with the default model security, then remove DENYs  
                IF @UseMemberSecurity <> 0 SET @SQL = @SQL + N'  
                    AND ISNULL(SR.Privilege_ID, ' + @strPrvID + N') <> 1';  
                  
                --Append the ORDER BY, substituting in the custom order-by for match() operations  
                SET @SQL += N'  
                    ORDER BY ';  
                IF (@matchOrderBy IS NOT NULL) BEGIN  
					SET @SQL += N'( CASE 
    WHEN ISNUMERIC('+@matchOrderBy+') = 1 THEN CONVERT(INT, '+@matchOrderBy+') 
    ELSE 9999999 -- or something huge
  END,'+
  @matchOrderBy + N') DESC, ';  
				END  
				IF (@SortOrderColumnQuoted IS NOT NULL) BEGIN  
                SET @SQL += N' CASE 
    WHEN ISNUMERIC('+@SortOrderColumnQuoted+') = 1 THEN CONVERT(INT, '+@SortOrderColumnQuoted+') 
    ELSE 9999999 -- or something huge
  END ' + N' ' + @SortDirection + N', ';  
            END   
                SET @SQL += N'T.ID ' + @SortDirection + N';';  
  
				--Complete the batch operation  
                SET @SQL = @SQL + N'  
                SET ROWCOUNT 0; -- set back to zero so it will not interfere with subsequent queries   
                ';  
                          
                SET @SQL = @SQL + N'  
                --Return just the page we are interested in      
                SELECT   
                    ' + @ColumnString + N'   
                FROM   
                    ' + @ViewName + N' AS T  
                INNER JOIN @TempItems AS SR ON (T.ID = SR.ID)';  
  
                SET @SQL = @SQL + N'  
                WHERE T.Version_ID = ' + @strVersion_ID + N'  
                    AND SR.RowNo >= ' + @strStartRow;  
  
                SET @SQL = @SQL + N'  
                ORDER BY SR.RowNo ASC';  
            END   
            ELSE BEGIN  
                SET @SQL = CASE WHEN @UseMemberSecurity <> 0 THEN + @MemberSecurityCTE ELSE + ' ' END + N'  
                --Returns all the records in the entity.                      
                SELECT   
                    ' + @ColumnString + N'   
                FROM   
                    ' + @ViewName + N' AS T' + @SQL;  
                  
                IF @SearchTerm IS NOT NULL SET @SQL = @SQL + N'  
                AND ' + @SearchTerm;  
            END              
  
        END ELSE BEGIN --Count only  
  
            SET @SQL = CASE WHEN @UseMemberSecurity <> 0 THEN + @MemberSecurityCTE ELSE + ' ' END + N'  
            SELECT @MemberCount = COUNT(DISTINCT T.ID)   
            FROM ' + @ViewNameRaw + N' AS T' + @SQL;  
            IF @UseMemberSecurity <> 0 BEGIN  
             SET @SQL = @SQL + N'  
                AND ISNULL(SR.Privilege_ID, ' + @strPrvID + N') <> 1';  
            END;   
            ELSE   
            BEGIN  
             -- Bug 358879 - given no member security filtering, must exclude items on deny.  
             SET @SQL = @SQL + N'  
                AND ' + @strPrvID + ' <> 1';  
            END;  
        END; --if                   
  
        SET @Mode = 2;  
  
    --Criterion 2: display unused members of the hierarchy (Hierarchy Explorer)      
    END ELSE IF @Parent_ID = -1 BEGIN  
  
        --Resolve for psbug 243221  
        IF (Select Count(ID) FROM mdm.tblHierarchy where Entity_ID = @Entity_ID and ID = @Hierarchy_ID) = 0  
        BEGIN  
            --On error, return NULL results  
            RAISERROR ('%s: Invalid Parameters.', 11, 1, @e);  
            RETURN(1);  
        END  
        --End Resolve  
  
        IF @UseMemberSecurity <> 0 AND @MemberType_ID = 1 BEGIN   
  
            SET @SQL = @SQL + N'  
            INNER JOIN membersresolved AS SR  
                ON SR.Member_ID = T.ID  
                AND SR.Version_ID = T.Version_ID   
                AND SR.MemberType_ID = ' + @strMemberType_ID;  
  
        END ELSE IF @UseMemberSecurity = 1 AND @MemberType_ID = 2 BEGIN  
  
            SET @SQL = @SQL + N'   
            LEFT JOIN membersresolved AS SR  
                ON SR.Member_ID = T.ID  
                AND SR.Version_ID = T.Version_ID   
                AND SR.MemberType_ID = ' + @strMemberType_ID;  
  
        END; --if  
  
        SET @SQL = @SQL + N'  
            WHERE T.Version_ID = ' + @strVersion_ID;  
          
        SET @SQL = @SQL + N'  
                AND T.ID NOT IN (  
                    SELECT Child_EN_ID   
                    FROM ' + @TableName + N' AS HR   
                    WHERE    
                        HR.Version_ID = ' + @strVersion_ID + N' AND   
                        HR.ChildType_ID = 1 AND  
                        HR.Status_ID = 1 AND    
                        HR.Hierarchy_ID = ' + @strHierarchy_ID;  
  
        IF @SearchTerm IS NOT NULL SET @SQL = @SQL + N'  
                        AND ' + @SearchTerm + N')  
                AND ' + @SearchTerm;  
        ELSE SET @SQL = @SQL + N')';  
  
        IF @CountOnly = 0 BEGIN  
  
            --First populate temporary table with IDs of rows  
            SET @SQL = N'  
            --Get keyset up to end of requested page  
            DECLARE @TempItems TABLE(RowNo INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, ID INT, Privilege_ID INT);' +   
            CASE WHEN @UseMemberSecurity <> 0 THEN @MemberSecurityCTE ELSE '' END + N'  
            --SET ROWCOUNT ' + CONVERT(NVARCHAR(30), CASE @PageSize WHEN 0 THEN 0 ELSE @StartRow + @PageSize END) + N';  
            INSERT INTO @TempItems(ID, Privilege_ID)  
            SELECT TOP ' + CONVERT(NVARCHAR(30), CASE @PageSize WHEN 0 THEN 0 ELSE @StartRow + @PageSize - 1 END) + ' T.ID, ' + CASE WHEN @UseMemberSecurity <> 0 THEN N'SR.Privilege_ID' ELSE N'NULL' END + N'  
            FROM ' + @ViewNameRaw + N' AS T' + @SQL;  
                          
            IF @UseMemberSecurity <> 0 SET @SQL = @SQL + N'  
                AND ISNULL(SR.Privilege_ID, ' + @strPrvID + N') <> 1';  
                  
            --Append the ORDER BY, substituting in the custom order-by for match() operations  
            SET @SQL += N'  
                ORDER BY ';  
            IF (@matchOrderBy IS NOT NULL)  
				SET @SQL += N'(' + @matchOrderBy + N') DESC, ';				  
            SET @SQL += N'T.Code ASC, T.ID ASC;';  
  
            --Get the page we are interested in  
            SET @SQL = @SQL + N'  
            --Return just the page we are interested in              
            --SET ROWCOUNT ' + @strPageSize + N';  
            SELECT   
                ' + @ColumnString + N'   
            FROM   
                ' + @ViewName + N' AS T  
            INNER JOIN @TempItems AS SR ON (T.ID = SR.ID)';  
  
            SET @SQL = @SQL + N'  
            WHERE T.Version_ID = ' + @strVersion_ID + N'  
                AND SR.RowNo >= ' + @strStartRow;  
  
            SET @SQL = @SQL + N'  
            ORDER BY SR.RowNo ASC';  
  
        END ELSE BEGIN --Count only  
  
            SET @SQL = CASE WHEN @UseMemberSecurity <> 0 THEN + @MemberSecurityCTE ELSE + ' ' END + N'  
            SELECT @MemberCount = COUNT(DISTINCT T.ID)   
            FROM ' + @ViewNameRaw + N' AS T' + @SQL;  
  
        END; --if  
          
        SET @Mode = 3;  
  
    --Criterion 3: display all members under another (Hierarchy Explorer)  
    END ELSE IF @Parent_ID IS NOT NULL AND (@Attribute_ID IS NULL OR @Attribute_ID = 0) BEGIN  
  
        IF @Parent_ID = 0 AND @UseMemberSecurity = 1 AND @RootSecured = 0 BEGIN  
  
            SET @SQL = @SQL + N'   
            INNER JOIN mdm.viw_SYSTEM_SECURITY_USER_MEMBER AS SU   
                ON SU.Member_ID = T.ID  
                AND T.Version_ID = SU.Version_ID  
                AND SU.User_ID = ' + @strUser_ID + N'   
                AND SU.MemberType_ID = ' + @strMemberType_ID + N'   
            INNER JOIN ' + @TableName + N' AS HR ON SU.Entity_ID = ' + @strEntity_ID + N'  
                AND SU.MemberType_ID = HR.ChildType_ID   
                AND SU.Member_ID = CASE HR.ChildType_ID WHEN 1 THEN HR.Child_EN_ID WHEN 2 THEN HR.Child_HP_ID END  
                AND SU.User_ID = ' + @strUser_ID;  
  
        END ELSE IF (@Parent_ID = 0 AND @UseMemberSecurity <> 1) OR @Parent_ID <> 0 OR @RootSecured = 1 BEGIN  
  
            SET @SQL = @SQL + N'  
            RIGHT JOIN ' + @TableName + N' AS HR ON T.ID = CASE HR.ChildType_ID WHEN 1 THEN HR.Child_EN_ID WHEN 2 THEN HR.Child_HP_ID END  
                AND HR.Version_ID = T.Version_ID   
                AND HR.Version_ID = ' + @strVersion_ID + N'   
                AND HR.ChildType_ID = ' + @strMemberType_ID;  
  
        END; --if  
  
        IF @UseMemberSecurity = 1 AND @CountOnly = 0 BEGIN  
  
            SET @SQL = @SQL + N'  
            LEFT JOIN membersresolved AS SR  
                ON SR.Member_ID = CASE HR.ChildType_ID WHEN 1 THEN HR.Child_EN_ID WHEN 2 THEN HR.Child_HP_ID END  
                AND SR.MemberType_ID = HR.ChildType_ID  
                AND SR.MemberType_ID = ' + @strMemberType_ID;  
  
        END ELSE IF @UseMemberSecurity = 2 BEGIN  
  
            SET @SQL = @SQL + N'  
            LEFT JOIN membersresolved AS SR  
                ON SR.Member_ID = CASE HR.ChildType_ID WHEN 1 THEN HR.Child_EN_ID WHEN 2 THEN HR.Child_HP_ID END  
                AND SR.MemberType_ID = HR.ChildType_ID  
                AND HR.ChildType_ID = 1   
                --AND SR.Entity_ID = ' + @strEntity_ID;  
  
        END; --if  
  
        SET @SQL = @SQL + N'  
            WHERE HR.Version_ID = ' + @strVersion_ID + N'  
                AND HR.Hierarchy_ID = ' + @strHierarchy_ID + N'   
                AND T.ID IS NOT NULL';  
  
        IF @RootSecured = 1 OR @Parent_ID <> 0 OR (@Parent_ID = 0 AND @UseMemberSecurity <> 1) SET @SQL = @SQL + N'  
                AND ISNULL(HR.Parent_HP_ID,0) = ' + @strParent_ID; --TODO: Revisit for perf  
  
        IF @UseMemberSecurity = 2 SET @SQL = @SQL + N'  
                AND ISNULL(SR.Privilege_ID, 0) <> CASE   
                    WHEN HR.ChildType_ID = 2 THEN -1   
                    WHEN HR.ChildType_ID = 1 THEN 0  
                END --case';  
  
        IF @SearchTerm IS NOT NULL SET @SQL = @SQL + N'  
                AND ' + @SearchTerm;  
  
        IF @CountOnly = 0 BEGIN  
            --First populate temporary table with IDs of rows  
            SET @SQL = N'  
            --Get keyset up to end of requested page  
            DECLARE @TempItems TABLE(RowNo INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, ID INT, Privilege_ID INT);' +   
            CASE WHEN @UseMemberSecurity <> 0 THEN + @MemberSecurityCTE ELSE + ' ' END + N'  
            --SET ROWCOUNT ' + CONVERT(NVARCHAR(30), CASE @PageSize WHEN 0 THEN 0 ELSE @StartRow + @PageSize END) + N';  
            INSERT INTO @TempItems(ID, Privilege_ID)  
            SELECT TOP ' + CONVERT(NVARCHAR(30), CASE @PageSize WHEN 0 THEN 0 ELSE @StartRow + @PageSize - 1 END) + ' T.ID, ' + CASE WHEN @UseMemberSecurity <> 0 THEN N'SR.Privilege_ID' ELSE N'NULL' END + N'  
            FROM ' + @ViewNameRaw + N' AS T' + @SQL;  
  
            IF @UseMemberSecurity <> 0 SET @SQL = @SQL + N'  
                AND ISNULL(SR.Privilege_ID, ' + @strPrvID + N') <> 1';  
  
            IF @SortColumnQuoted LIKE CAST(N'_ValidationStatus_ID_' AS sysname) SET @SortColumnQuoted = quotename(CAST(N'T.[ValidationStatus_ID]' AS sysname));  
  
            --Append the ORDER BY, substituting in the custom order-by for match() operations  
            SET @SQL += N'  
                ORDER BY ';                 
            IF (@matchOrderBy IS NOT NULL) BEGIN  
				SET @SQL += N'(' + @matchOrderBy + N') DESC, ';  
			END  
			IF (@SortOrderColumnQuoted IS NOT NULL) BEGIN  
                SET @SQL += N' CASE 
    WHEN ISNUMERIC('+@SortOrderColumnQuoted+') = 1 THEN CONVERT(INT, '+@SortOrderColumnQuoted+') 
    ELSE 9999999 -- or something huge
  END ' + N' ' + @SortDirection + N', ';  
            END  
            SET @SQL += N'HR.SortOrder, T.ID ' + @SortDirection + N';';  
  
            --Get Remaining Records  
            SET @SQL = @SQL + N'  
            --Return just the page we are interested in  
            --SET ROWCOUNT ' + @strPageSize + N';  
            SELECT   
                ' + @ColumnString + N'   
            FROM   
                ' + @ViewName + N' AS T  
            INNER JOIN @TempItems AS SR ON (T.ID = SR.ID)';  
  
            SET @SQL = @SQL + N'  
            WHERE T.Version_ID = ' + @strVersion_ID + N'  
                AND SR.RowNo >= ' + @strStartRow;  
  
            SET @SQL = @SQL + N'  
            ORDER BY SR.RowNo ASC';  
          
        END ELSE BEGIN --Count only  
  
            SET @SQL = CASE WHEN @UseMemberSecurity <> 0 THEN + @MemberSecurityCTE ELSE + ' ' END + N'  
            SELECT @MemberCount = COUNT(DISTINCT T.ID)   
            FROM ' + @ViewNameRaw + N' AS T' + @SQL;  
  
        END; --if  
  
        SET @Mode = 4;  
      
    --Criterion 4: display all members assigned to the node (Hierarchy Explorer - Derived)  
    END ELSE IF (@Attribute_ID IS NOT NULL AND @AttributeValue IS NOT NULL) AND (@Attribute_ID <> 0 AND @AttributeValue <> 0) BEGIN --from the Derived Hierarchy  
        DECLARE   
            @AttributeName           sysname,  
            @TempAttributeValue      NVARCHAR(250),  
            @AttributeEntity_ID      INT,  
            @CurrentLevel_ID         INT,  
            @PriorLevel_ID           INT,  
            @PriorItem_ID            INT,  
            @PriorItemType_ID        INT,  
            @SQLFrom                 NVARCHAR(MAX), --Used for getting all the join tables when skipping levels in DH  
            @TempItem_ID             INT,  
            @TempItemType_ID         INT,  
            @TempLookupEntity_ID     INT,  
            @TempLookupViewName      sysname,  
            @TempLookupPriorViewName sysname,  
            @TempLookupAttributeName sysname,  
            @TempTotalCounter        INT,  
            @TempCounter             INT;  
        DECLARE @TempTable           TABLE (Item_ID INT NOT NULL, ItemType_ID INT NOT NULL, Level_ID INT NOT NULL);  
  
        --Fetch the attribute DBA column name  
        SELECT   
            @AttributeName = [Name],  
            @AttributeEntity_ID = DomainEntity_ID   
        FROM mdm.tblAttribute WHERE ID = @Attribute_ID;  
  
        --Fetch the code from the ID            
        EXEC mdm.udpMemberDisplayCodeGetByID @Version_ID, @AttributeEntity_ID, @AttributeValue, 1, 1, @TempAttributeValue OUTPUT;  
  
        --Check to see if any levels were skipped; if so, create the proper join string------------         
        --Fetch the prior level  
        SELECT TOP 1 @PriorLevel_ID = Level_ID   
        FROM mdm.tblDerivedHierarchyDetail   
        WHERE DerivedHierarchy_ID = @Hierarchy_ID AND Foreign_ID = @Attribute_ID  
        ORDER BY Level_ID DESC;  
  
        --Fetch the current level  
        SELECT TOP 1 @CurrentLevel_ID = Level_ID   
        FROM mdm.tblDerivedHierarchyDetail   
        WHERE DerivedHierarchy_ID = @Hierarchy_ID AND Level_ID < @PriorLevel_ID AND IsVisible = 1   
        ORDER BY Level_ID DESC;                        
  
        --Fetch the list of tables to join (if skipping levels)  
        SELECT  
            @SQLFrom = CAST(N'' AS NVARCHAR(max)),  
            @TempCounter = 0,       
            @TempLookupPriorViewName = CAST(N''  AS sysname);  
  
        INSERT INTO @TempTable SELECT Foreign_ID, ForeignType_ID, Level_ID FROM mdm.tblDerivedHierarchyDetail   
        WHERE DerivedHierarchy_ID = @Hierarchy_ID AND Level_ID BETWEEN @CurrentLevel_ID AND @PriorLevel_ID   
        AND Level_ID != @CurrentLevel_ID AND Level_ID != @PriorLevel_ID  
        ORDER BY Level_ID ASC;  
          
        SELECT @TempTotalCounter = COUNT(*) FROM @TempTable;  
          
        WHILE EXISTS(SELECT 1 FROM @TempTable) BEGIN  
            SET @TempCounter = @TempCounter + 1;  
              
            SELECT TOP 1 @TempItem_ID = Item_ID, @TempItemType_ID = ItemType_ID FROM @TempTable ORDER BY Level_ID ASC;  
              
            SET @TempLookupEntity_ID = CASE @TempItemType_ID  
                WHEN 1 THEN (SELECT DomainEntity_ID FROM mdm.tblAttribute WHERE ID = @TempItem_ID)  
                WHEN 0 THEN @TempItem_ID  
            END; --case  
  
            SET @TempLookupViewName = mdm.udfViewNameGetByID(@TempLookupEntity_ID, 1, 0);  
      
            --Determine attribtue DBA column name            
            SELECT @TempLookupAttributeName = [Name] FROM mdm.tblAttribute WHERE ID = @TempItem_ID;  
                  
            --Build join table list  
            IF LEN(@TempLookupPriorViewName) =0   
            SET @SQLFrom = @SQLFrom + N'  
            INNER JOIN mdm.' + quotename(@TempLookupViewName) + N' AS ' + quotename(@TempLookupViewName) + N' ON ' + quotename(@TempLookupViewName) + N'.Code = T.' + quotename(@TempLookupAttributeName);  
            ELSE SET @SQLFrom = @SQLFrom + N'   
            INNER JOIN mdm.' + quotename(@TempLookupViewName) + N' AS ' + quotename(@TempLookupViewName) + N' ON ' + quotename(@TempLookupViewName) + N'.Code = ' + quotename(@TempLookupPriorViewName) + N'.' + quotename(@TempLookupAttributeName);  
              
                  
            SET @SQLFrom = @SQLFrom + N'  
                AND ' + quotename(@TempLookupViewName) + N'.Version_ID = T.Version_ID   
                AND T.Version_ID = ' + @strVersion_ID;  
  
            --If last table to join then add the final portion of the join  
            IF @TempCounter = @TempTotalCounter  
                SET @SQLFrom = @SQLFrom + N'  
                AND ' + quotename(@TempLookupViewName) + N'.' + quotename(@AttributeName) + N' = @Value'  
  
            SET @TempLookupPriorViewName = @TempLookupViewName;  
            DELETE FROM @TempTable WHERE Item_ID = @TempItem_ID;  
        END; --while  
              
        -------------------------------------------------------------------------------------------  
        IF @Attribute_ID IS NULL AND @UseMemberSecurity = 1 SET @SQL = @SQL + N'  
            INNER JOIN mdm.viw_SYSTEM_SECURITY_USER_MEMBER AS SU ON SU.Member_ID = T.ID  
                AND T.Version_ID =  SU.Version_ID  
                AND SU.User_ID = ' + @strUser_ID + N'   
                AND SU.MemberType_ID = ' + @strMemberType_ID;  
  
        IF @CountOnly = 0 AND @UseMemberSecurity = 1 BEGIN  
            SET @SQL = @SQL + N'  
            LEFT JOIN membersresolved AS SR  
                ON SR.Member_ID = T.ID  
                AND SR.MemberType_ID = ' + @strMemberType_ID;  
        END ELSE IF @UseMemberSecurity = 2 BEGIN  
            SET @SQL = @SQL + N'   
            INNER JOIN membersresolved SR  
                ON SR.Member_ID = T.ID  
                AND SR.MemberType_ID = ' + @strMemberType_ID;  
        END; --if  
  
        IF LEN(@SQLFrom) <> 0  
            SET @SQL = @SQL + @SQLFrom;  
        ELSE BEGIN  
            SET @SQL = @SQL + N'   
            WHERE T.Version_ID = ' + @strVersion_ID + N'  
                AND ' + quotename(@AttributeName) + N' = @Value';  
            IF @CountOnly = 0 AND @UseMemberSecurity <> 0 SET @SQL = @SQL + N'  
                AND ISNULL(SR.Privilege_ID, ' + @strPrvID + N') <> 1 ';  
        END; --if  
  
        IF @SearchTerm IS NOT NULL SET @SQL = @SQL + N'   
                AND ' + @SearchTerm;  
  
        IF @CountOnly = 0 BEGIN  
              
            --build a snippet to get the unique member Ids for the current page number and size  
            DECLARE @idFilter NVARCHAR(MAX);    
              SET @idFilter = N'  
                INSERT INTO @TempIds(ID, Privilege_ID)  
                SELECT TOP ' + CONVERT(NVARCHAR(30), CASE @PageSize WHEN 0 THEN 0 ELSE @StartRow + @PageSize - 1 END) + ' Q.ID, ' + CASE WHEN @UseMemberSecurity <> 0 THEN N'Q.Privilege_ID' ELSE N'NULL Privilege_ID' END + N'    
                FROM  
                (SELECT DISTINCT T.[ID], ' + CASE WHEN @UseMemberSecurity <> 0 THEN N'SR.Privilege_ID' ELSE N'NULL Privilege_ID' END;  
	          IF (@SortOrderColumnQuoted IS NOT NULL) BEGIN  
	            -- Add the sort column to the result set, so it can be used in the ORDER BY clause.    
                SET @idFilter += N', ' + (CASE @SortOrderColumnQuoted WHEN N'[Name]' THEN N'[Member_Name]' WHEN N'[Code]' THEN N'[Member_Code]' ELSE @SortOrderColumnQuoted END);    
              END                
              SET @idFilter += N'  
                FROM ' + @ViewName + N' AS T' + @SQL + N') Q  
                ORDER BY ';  
	          IF (@SortOrderColumnQuoted IS NOT NULL) BEGIN    
                SET @idFilter += N'Q.' + (CASE @SortOrderColumnQuoted WHEN N'[Name]' THEN N'[Member_Name]' WHEN N'[Code]' THEN N'[Member_Code]' ELSE @SortOrderColumnQuoted END) + N' ' + @SortDirection + N', ';    
              END                            
              SET @idFilter += N'Q.ID ' + @SortDirection + N';  
              ';			      
			    
             
             --First populate 2 temporary tables, 1 with unique IDs of members to display  
             --the other containing the many rows neccesary for building the member rows for the current hierarchy  
            SET @SQL = N'    
            --Get keyset up to end of requested page    
            DECLARE @TempItems TABLE(RowNo INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, ID INT, Privilege_ID INT);  
            DECLARE @TempIds TABLE(RowNo INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, ID INT, Privilege_ID INT);  
              
            ' +     
            CASE WHEN @UseMemberSecurity <> 0 THEN + @MemberSecurityCTE ELSE + ' ' END + @idFilter + N'  
            ' +  
            CASE WHEN @UseMemberSecurity <> 0 THEN + @MemberSecurityCTE ELSE + ' ' END + N'  
            INSERT INTO @TempItems(ID, Privilege_ID)    
            SELECT  T.ID, Q.Privilege_ID  
            FROM ' + @ViewName + N' AS T  
            INNER JOIN @TempIds Q ON Q.ID = T.ID   
            ' + @SQL + N' AND Q.RowNo >= ' + @strStartRow;    
      
            SET @SQL += N'    
            ORDER BY ';			  
			IF (@SortOrderColumnQuoted IS NOT NULL) BEGIN  
                SET @SQL += N' CASE 
    WHEN ISNUMERIC('+@SortOrderColumnQuoted+') = 1 THEN CONVERT(INT, '+@SortOrderColumnQuoted+') 
    ELSE 9999999 -- or something huge
  END ' + N' ' + @SortDirection + N', ';  
            END   
              
            SET @SQL += N'T.ID ' + @SortDirection + N';';    
    
            --Get Remaining Records    
            SET @SQL = @SQL + N'    
            SELECT     
                ' + @ColumnString + N'     
            FROM     
                ' + @ViewName + N' AS T    
            INNER JOIN @TempItems AS SR ON (T.ID = SR.ID)';    
    
            SET @SQL = @SQL + N'    
            WHERE T.Version_ID = ' + @strVersion_ID;  
			  
            SET @SQL = @SQL + N'    
            ORDER BY SR.RowNo ASC';  
        END ELSE BEGIN --Count only  
  
            SET @SQL = CASE WHEN @UseMemberSecurity <> 0 THEN + @MemberSecurityCTE ELSE + ' ' END + N'  
            SELECT @MemberCount = COUNT(DISTINCT T.ID)   
            FROM ' + @ViewName + N' AS T' + @SQL;  
  
        END; --if  
  
        SET @Mode = 5;  
    END; --if  
  
      
    /*  
    ------------------------------------  
    EXECUTE SQL  
    ------------------------------------  
    */  
    --PRINT @Mode  
    PRINT @SQL;  
    IF @Mode = 2 AND @CountOnly = 1 AND @SearchTerm IS NULL AND @Member_ID IS NULL BEGIN  
		-- Try to get the results from the last call, as cached in tblUserMemberCount.  
		EXEC mdm.udpUserMemberLastCountGet @User_ID,@Version_ID,@Entity_ID,@MemberType_ID, @MemberCount OUTPUT;    
		IF @MemberCount = -1 BEGIN    
			-- Couldn't find the results in the cache, so compute the count and save it in the cache.  
			EXEC sp_executesql @SQL, N'@SearchTable mdm.MemberGetCriteria READONLY, @MemberCount INT OUTPUT', @SearchTable, @MemberCount OUTPUT;    
			EXEC mdm.udpUserMemberLastCountSave @User_ID, @Version_ID, @Entity_ID, @MemberType_ID, @MemberCount;    
		END; --if            
    END ELSE IF @Mode = 5 BEGIN  
        IF @CountOnly = 1 BEGIN  
            EXEC sp_executesql @SQL, N'@SearchTable mdm.MemberGetCriteria READONLY, @Value NVARCHAR(250), @MemberCount INT OUTPUT', @SearchTable, @TempAttributeValue, @MemberCount OUTPUT;  
        END ELSE BEGIN  
            EXEC sp_executesql @SQL, N'@SearchTable mdm.MemberGetCriteria READONLY, @Value NVARCHAR(250)',  @SearchTable, @TempAttributeValue;  
        END; --if  
    END ELSE IF @CountOnly = 1 BEGIN  
        EXEC sp_executesql @SQL, N'@SearchTable mdm.MemberGetCriteria READONLY, @MemberCount INT OUTPUT', @SearchTable ,@MemberCount OUTPUT;  
    END ELSE BEGIN  
        EXEC sp_executesql @SQL, N'@SearchTable mdm.MemberGetCriteria READONLY', @SearchTable;  
    END; --if  
    SET NOCOUNT OFF;  
END;