1 USE [GPP]  2 GO  3 /****** Object:  StoredProcedure [dbo].[P_V_USER_INFO_ROLE_SCHEME_S_PAGE_BY_APPID_ROLEID]    Script Date: 14/11/2014 10:10:36 AM ******/  4 SET ANSI_NULLS ON  5 GO  6 SET QUOTED_IDENTIFIER ON  7 GO  8 ALTER PROC [dbo].[P_V_USER_INFO_ROLE_SCHEME_S_PAGE_BY_APPID_ROLEID]  9 ( 10 @p_app_id nvarchar(50), 11 @p_role_id nvarchar(50), 12 @page_index int, 13 @page_size  int 14 ) 15 as 16 WITH PageIndex AS  17 ( SELECT TOP (@page_index+@page_size) ROW_NUMBER() OVER (ORDER BY LAST_ACTIVITY_TIME)  18 as RowIndex,  19        [USER_ID] 20  21       ,[APP_ID] 22  23       ,[USER_NAME] 24  25       ,[LOWERED_USER_NAME] 26  27       ,[MOBILE_ALIAS] 28  29       ,[IS_ANONYMOUS] 30  31       ,[LAST_ACTIVITY_TIME] 32  33       ,[FLAG] 34  35       ,[AD_ID] 36  37       ,[NRIC] 38  39       ,[DESIGNATION] 40  41       ,[AGENCY_ID] 42  43       ,[ADDRESS] 44  45       ,[AGE] 46  47       ,[EMAIL_ADDRESS] 48  49       ,[ROLE_ID] 50  51       ,[ROLE_NAME] 52  53       ,[SCHEME_ID] 54  55       ,[SCHEME_CODE] 56  57       ,[SCHEME_NAME] 58  59       ,[IS_ACTIVE] 60  61       ,[USER_IN_ROLE_ID] 62  63       ,[ROLE_IS_DELETED] 64       ,[USER_IS_DELETED] 65  66       ,[CAN_VIEW_VIP] 67  68       ,[IS_POSTQA_VERIFIER] 69  70       ,[UF_VERSION_NO] 71  72       ,[U_VERSION_NO] 73  74       ,[UIR_VERSION_NO] 75  76       ,[FULL_NAME] 77  78       ,[dbo].[F_T_USER_IN_ROLE_S_SCHEMEROLE_BY_USERID]([USER_ID]) AS SCHEME_ROLE 79  80     FROM [V_USER_INFO_ROLE_SCHEME]  81  82     WHERE  83  84     APP_ID=@p_app_id AND ROLE_ID=@p_role_id and USER_IS_DELETED=0 )  85 SELECT 86       [USER_ID] 87  88       ,[APP_ID] 89  90       ,[USER_NAME] 91  92       ,[LOWERED_USER_NAME] 93  94       ,[MOBILE_ALIAS] 95  96       ,[IS_ANONYMOUS] 97  98       ,[LAST_ACTIVITY_TIME] 99 100       ,[FLAG]101 102       ,[AD_ID]103 104       ,[NRIC]105 106       ,[DESIGNATION]107 108       ,[AGENCY_ID]109 110       ,[ADDRESS]111 112       ,[AGE]113 114       ,[EMAIL_ADDRESS]115 116       ,[ROLE_ID]117 118       ,[ROLE_NAME]119 120       ,[SCHEME_ID]121 122       ,[SCHEME_CODE]123 124       ,[SCHEME_NAME]125 126       ,[IS_ACTIVE]127 128       ,[USER_IN_ROLE_ID]129 130       ,[ROLE_IS_DELETED]131       ,[USER_IS_DELETED]132 133       ,[CAN_VIEW_VIP]134 135       ,[IS_POSTQA_VERIFIER]136 137       ,[UF_VERSION_NO]138 139       ,[U_VERSION_NO]140 141       ,[UIR_VERSION_NO]142 143       ,[FULL_NAME] 144 145       ,[dbo].[F_T_USER_IN_ROLE_S_SCHEMEROLE_BY_USERID]([USER_ID]) AS SCHEME_ROLE146     147       FROM PageIndex WHERE RowIndex >@page_index AND RowIndex <= (@page_index+@page_size) ORDER BY LAST_ACTIVITY_TIME148 149 SELECT COUNT(*) AS TotalRowCount FROM 150 (151 SELECT [USER_ID] FROM V_USER_INFO_ROLE_SCHEME  152 WHERE APP_ID=@p_app_id AND ROLE_ID=@p_role_id and USER_IS_DELETED=0 153 GROUP BY USER_ID)X

 1 CREATE FUNCTION [dbo].[F_T_USER_IN_ROLE_S_SCHEMEROLE_BY_USERID] 2  3 ( 4  5        @USER_ID VARCHAR(50) 6  7 ) 8  9 RETURNS VARCHAR(1000)10 11 AS12 13   BEGIN14 15        DECLARE @SchemeRole VARCHAR(1000) = ‘‘16 17 18 19        SELECT @SchemeRole = @SchemeRole + s.SCHEME_NAME + ‘-‘ + r.ROLE_NAME + ‘;<br/>‘20 21        FROM dbo.T_USER_IN_ROLE AS ur22 23        INNER JOIN dbo.T_SCHEME AS s ON ur.SCHEME_ID = s.SCHEME_ID24 25        INNER JOIN dbo.T_IC_ROLES AS r ON ur.ROLE_ID = r.ROLE_ID26 27        WHERE ur.USER_ID = @USER_ID and ur.IS_DELETED=028 29 30 31     RETURN @SchemeRole32 33   END

SELECT              USER_ID,  STUFF  ((SELECT ‘;‘ +  (CAST(ROLE_NAME AS VARCHAR(MAX))+‘,‘+CAST(SCHEME_CODE AS VARCHAR(MAX)))    FROM V_USER_INFO_ROLE_SCHEME    WHERE      (USER_ID = Results.USER_ID)    FOR      XML      PATH (‘‘)),1,1,‘‘  ) AS SCHEME_ROLE     FROM [V_USER_INFO_ROLE_SCHEME]  as Results  group by  USER_ID