常用SQL语句3（MSSQL）

--IF EXISTS FUNCTION fn_GET_PRESENT_STS THEN DORP IT OR CREATE IT
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fn_GET_PRESENT_STS')
    DROP FUNCTION [dbo].[fn_GET_PRESENT_STS]
GO

CREATE FUNCTION fn_GET_PRESENT_STS(@p_status INT) 
RETURNS VARCHAR(60) 
AS 
BEGIN 
DECLARE @l_rtn varchar(60) 
SET 
  @l_rtn=CASE 
      WHEN @p_status=0 THEN 'Rejected' 
      WHEN @p_status=1 THEN 'Accepted'
      WHEN @p_status=2 THEN 'Pending'
      WHEN @p_status=3 THEN 'Submitting'
      WHEN @p_status=4 THEN 'Error'
      WHEN @p_status=5 THEN 'Cleared'
      WHEN @p_status=6 THEN 'Clearing'
      ELSE 'N/A' 
      END 
  RETURN @l_rtn 
END
GO

--IF EXISTS FUNCTION fn_GET_ACC_VERIFY_STS THEN DORP IT OR CREATE IT
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fn_GET_ACC_VERIFY_STS')
    DROP FUNCTION [dbo].[fn_GET_ACC_VERIFY_STS]
GO

CREATE FUNCTION fn_GET_ACC_VERIFY_STS(@acc_status INT) 
RETURNS VARCHAR(60) 
AS 
BEGIN 
DECLARE @l_rtn varchar(60) 
SET 
  @l_rtn=CASE 
      WHEN @acc_status=0 THEN 'Mismatched' 
      WHEN @acc_status=1 THEN 'Matched'
      WHEN @acc_status=2 THEN 'Undetermined'
      WHEN @acc_status=3 THEN 'Rejected'
      WHEN @acc_status=4 THEN 'Pending'
      ELSE 'N/A' 
      END 
  RETURN @l_rtn 
END
GO

--IF EXISTS FUNCTION fn_GET_CRD_STS THEN DORP IT OR CREATE IT
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fn_GET_CRD_STS')
    DROP FUNCTION [dbo].[fn_GET_CRD_STS]
GO

CREATE FUNCTION fn_GET_CRD_STS(@crd_status INT) 
RETURNS VARCHAR(60) 
AS 
BEGIN 
DECLARE @l_rtn varchar(60) 
SET 
  @l_rtn=CASE 
      WHEN @crd_status=0 THEN 'Not Granted' 
      WHEN @crd_status=1 THEN 'Granted'
      WHEN @crd_status=2 THEN 'Reverted'
      WHEN @crd_status=3 THEN 'Error'
      WHEN @crd_status=4 THEN 'Submitting'
      ELSE 'N/A' 
      END 
  RETURN @l_rtn 
END
GO

--IF EXISTS FUNCTION fn_GET_CHANNEL THEN DORP IT OR CREATE IT
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fn_GET_CHANNEL')
    DROP FUNCTION [dbo].[fn_GET_CHANNEL]
GO

CREATE FUNCTION fn_GET_CHANNEL(@cust_1 VARCHAR(10), @p_tp INT ) 
RETURNS VARCHAR(60) 
AS 
BEGIN 
DECLARE @l_rtn varchar(60) 
SET 
  @l_rtn=CASE 
      WHEN @cust_1='IB' THEN 'iBanking' 
      WHEN @cust_1='FC' THEN 'FCDB'
      WHEN @cust_1='CW' THEN 'Corp Website'
      WHEN @cust_1 IS NULL AND @p_tp=2 THEN 'HKICL Portal'
      ELSE @cust_1 
      END 
  RETURN @l_rtn 
END
GO

--IF EXISTS FUNCTION fn_GET_GROUP THEN DORP IT OR CREATE IT
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fn_GET_GROUP')
    DROP FUNCTION [dbo].[fn_GET_GROUP]
GO

CREATE FUNCTION fn_GET_GROUP(@cust_2 VARCHAR(20)) 
RETURNS VARCHAR(60) 
AS 
BEGIN 
DECLARE @l_rtn varchar(60) 
SET 
  @l_rtn=CASE 
      WHEN @cust_2='PR' THEN 'Personal' 
      WHEN @cust_2='BB' THEN 'Business Banking'
      WHEN @cust_2='CP' THEN 'Corporate'
      ELSE @cust_2 
      END 
  RETURN @l_rtn 
END
GO

--IF EXISTS FUNCTION fn_GET_PRODUCT THEN DORP IT OR CREATE IT
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fn_GET_PRODUCT')
    DROP FUNCTION [dbo].[fn_GET_PRODUCT]
GO

CREATE FUNCTION fn_GET_PRODUCT(@cust_3 VARCHAR(20)) 
RETURNS VARCHAR(60) 
AS 
BEGIN 
DECLARE @l_rtn varchar(60) 
SET 
  @l_rtn=CASE 
      WHEN @cust_3='CASA' THEN 'CASA' 
      WHEN @cust_3='CC' THEN 'Credit Card'
      WHEN @cust_3='LOAN' THEN 'Loans'
      WHEN @cust_3='INSU' THEN 'Insurance'
      ELSE @cust_3 
      END 
  RETURN @l_rtn 
END
GO

--IF EXISTS FUNCTION fn_GET_RETURN_REASON DORP IT OR CREATE IT
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fn_GET_RETURN_REASON')
    DROP FUNCTION [dbo].[fn_GET_RETURN_REASON]
GO

CREATE FUNCTION fn_GET_RETURN_REASON(@present_val INT) 
RETURNS VARCHAR(100) 
AS 
BEGIN 
DECLARE @l_rtn varchar(100) 
SET 
  @l_rtn=CASE 
      WHEN @present_val=1 THEN 'Refer To Drawer (' + cast(@present_val as varchar(50)) + ')' 
      WHEN @present_val=3 THEN 'Drawn against uncollected funds (' +cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=4 THEN 'Payer Account Closed (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=6 THEN 'e-Cheque Is Out-of-date (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=7 THEN 'Post-dated e-Cheque (' + cast(@present_val as varchar(50)) + ')' 
      WHEN @present_val=8 THEN 'Payment Countermanded By The Drawer (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=26 THEN 'Not Drawn On This Bank (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=27 THEN 'Others (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=28 THEN 'Duplicated Presentment (' + cast(@present_val as varchar(50)) + ')' 
      WHEN @present_val=29 THEN 'Invalid e-Cheque (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=30 THEN 'e-Cheque Already Drawn (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=51 THEN 'Invalid Payee Account Or Unsupported Currency (' +cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=52 THEN 'Payee Account Not Exists (' + cast(@present_val as varchar(50)) + ')' 
      WHEN @present_val=53 THEN 'Invalid Payee (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=54 THEN 'Invalid Payer Branch (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=55 THEN 'Invalid Payer Account (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=56 THEN 'Invalid Payee Bank (' + cast(@present_val as varchar(50)) + ')' 
      WHEN @present_val=57 THEN 'Invalid Payer Bank (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=58 THEN 'e-Cheque Issued In This Currency Is Not Accepted By Payee Account (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=61 THEN 'Invalid File Type (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=62 THEN 'File Size Exceeded The Limits (' + cast(@present_val as varchar(50)) + ')' 
      WHEN @present_val=63 THEN 'Might Be A Fake e-Cheque (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=64 THEN 'Invalid e-Cheque (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=65 THEN 'Unsupported Currency (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=71 THEN 'Presentment Already Accepted (' + cast(@present_val as varchar(50)) + ')' 
      WHEN @present_val=72 THEN 'Duplicated Presentment Reference (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=73 THEN 'Invalid Presentment Reference (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=74 THEN 'Presentment Return Already Accepted (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=75 THEN 'Presentment Not Exists Or Not Accepted Previously (' + cast(@present_val as varchar(50)) + ')' 
      WHEN @present_val=76 THEN 'Presentment Already Submitted To Clearing (' + cast(@present_val as varchar(50)) + ')'
      WHEN @present_val=99 THEN 'Please Contact Bank (' + cast(@present_val as varchar(50)) + ')'
      ELSE 'null' 
      END 
  RETURN @l_rtn 
END
GO

--IF EXISTS FUNCTION fn_GET_ACTIVITY_NAME DORP IT OR CREATE IT
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fn_GET_ACTIVITY_NAME')
    DROP FUNCTION [dbo].[fn_GET_ACTIVITY_NAME]
GO

CREATE FUNCTION fn_GET_ACTIVITY_NAME(@mod_act INT, @mod_id INT) 
RETURNS VARCHAR(30) 
AS 
BEGIN 
DECLARE @l_rtn varchar(30) 
SET 
  @l_rtn=CASE 
      WHEN @mod_act=5 AND @mod_id <> 107 THEN 'Edit' 
      WHEN @mod_act=5 AND @mod_id=107 THEN 'Presentment Update'
      WHEN @mod_act=6 THEN 'Remove'
      WHEN @mod_act=12 THEN 'Handle Case'
      WHEN @mod_act=18 THEN 'Add/Edit'
      WHEN @mod_act=170 THEN 'Handle Case'
      WHEN @mod_act=180 THEN 'Presentment Update'
      ELSE STR(@mod_act) 
      END 
  RETURN @l_rtn 
END
GO

--IF EXISTS FUNCTION fn_GET_ACT_STATUS DORP IT OR CREATE IT
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fn_GET_ACT_STATUS')
    DROP FUNCTION [dbo].[fn_GET_ACT_STATUS]
GO

CREATE FUNCTION fn_GET_ACT_STATUS(@act_status INT) 
RETURNS VARCHAR(30) 
AS 
BEGIN 
DECLARE @l_rtn varchar(30) 
SET 
  @l_rtn=CASE 
      WHEN @act_status=0 THEN 'Fail' 
      WHEN @act_status=1 THEN 'Success'
      WHEN @act_status=2 THEN 'Reject'
      WHEN @act_status=3 THEN 'Approve'
      ELSE STR(@act_status) 
      END 
  RETURN @l_rtn 
END
GO

--IF EXISTS FUNCTION fn_GET_MOD_NAME DORP IT OR CREATE IT
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fn_GET_MOD_NAME')
    DROP FUNCTION [dbo].[fn_GET_MOD_NAME]
GO

CREATE FUNCTION fn_GET_MOD_NAME(@module INT) 
RETURNS VARCHAR(30) 
AS 
BEGIN 
DECLARE @l_rtn varchar(30) 
SET 
  @l_rtn=CASE 
      WHEN @module=0 THEN 'Name Matching Management' 
      WHEN @module=1 THEN 'Presentment Management'
      ELSE STR(@module) 
      END 
  RETURN @l_rtn 
END
GO

--IF EXISTS FUNCTION fn_GET_STATUS DORP IT OR CREATE IT
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fn_GET_STATUS')
    DROP FUNCTION [dbo].[fn_GET_STATUS]
GO

CREATE FUNCTION fn_GET_STATUS(@status INT) 
RETURNS VARCHAR(30) 
AS 
BEGIN 
DECLARE @l_rtn varchar(30) 
SET 
  @l_rtn=CASE 
      WHEN @status=0 THEN 'Suspend' 
      WHEN @status=1 THEN 'Active'
      WHEN @status=2 THEN 'Locked'
      ELSE STR(@status) 
      END 
  RETURN @l_rtn 
END
GO


--IF EXISTS VIEW PRE_ITM_DETAIL_VW THEN DORP IT OR CREATE IT
IF EXISTS(SELECT 1 FROM sys.views WHERE name='PRE_ITM_DETAIL_VW')
DROP VIEW PRE_ITM_DETAIL_VW
GO

CREATE VIEW PRE_ITM_DETAIL_VW 
AS
SELECT 
    pi.P_PAYER_BANK_COD AS PAYER_BANK_CODE, 
    pi.P_PAYEE_BRANCH AS PAYER_BRANCH_CODE,
    REPLACE(pi.P_PAYEE_ACC,'-','') as PAYEE_ACC_NUM, 
    pi.P_CHQ_NUM AS CHQ_NUM, 
    pi.P_CUR AS CHQ_CURRENCY,
    pi.P_NEXT_DAY AS NEXT_DAY, 
    pi.P_AMT AS AMOUNT, 
    pi.P_PRESENT_AT AS PERSENT_DATE, 
    pi.P_REF AS PRE_REF, 
    pi.P_BANK_REF AS BANK_REF, 
    [dbo].[fn_GET_PRESENT_STS](pi.P_PRESENT_STS) AS PRE_STATUS,
    [dbo].[fn_GET_ACC_VERIFY_STS](pi.P_ACC_VERIFY_STS) AS ACC_VERIFY_STATUS, 
    [dbo].[fn_GET_GROUP](pi.P_CUST_2) AS CUSTOMER_GROUP, 
    [dbo].[fn_GET_PRODUCT](pi.P_CUST_3) AS PRODUCT, 
    [dbo].[fn_GET_CHANNEL](pi.P_CUST_1, pi.P_TP) AS CHANNEL, 
    [dbo].[fn_GET_RETURN_REASON](pi.P_PRESENT_VAL) AS EXCEPTION_REASON, 
    [dbo].[fn_GET_CRD_STS](pi.P_CRD_STS) AS TRANS_STATUS, 
    pi.P_CLEAR_DT AS CLEAR_PROCESS_DT, 
    pi.P_CREATE_AT AS DEPOSIT_TIME, 
    pi.P_EXP_CLEAR_DT AS EXP_CLEAR_DT
FROM 
    CHQ_PRE_ITM_TBL pi
GO

--IF EXISTS VIEW PRE_GROUP_SUMMARY_VW THEN DORP IT OR CREATE IT
IF EXISTS(SELECT 1 FROM sys.views WHERE name='PRE_GROUP_SUMMARY_VW')
DROP VIEW PRE_GROUP_SUMMARY_VW
GO

CREATE VIEW PRE_GROUP_SUMMARY_VW
AS
SELECT 
    v.CUSTOMER_GROUP, v.CHANNEL, v.PRESENTED, v.PENDING, v.ERROR, v.CLEARED, v.ACCEPTED, v.REJECTED, v.RETURNED, ACCEPTED+REJECTED+RETURNED as COUNTTOTAL,
    v.ACCEPTEDAMOUNT, v.REJECTEDAMOUNT, v.RETURNEDAMOUNT, ACCEPTEDAMOUNT+REJECTEDAMOUNT+RETURNEDAMOUNT as AMOUNTTOTAL     
FROM (
    SELECT 
        pi.CUSTOMER_GROUP AS CUSTOMER_GROUP, pi.CHANNEL AS CHANNEL, 
        SUM(CASE WHEN pi.PRE_STATUS IN ('Accepted', 'Cleared', 'Clearing') OR pi.PRE_STATUS = 'Rejected' AND pi.PERSENT_DATE IS NOT NULL THEN 1 ELSE 0 end) AS PRESENTED,
        SUM(CASE WHEN pi.PRE_STATUS = 'Pending' THEN 1 ELSE 0 END) AS PENDING,
        SUM(CASE WHEN pi.PRE_STATUS = 'Accepted' THEN 1 ELSE 0 END) AS ACCEPTED,
        SUM(CASE WHEN pi.PRE_STATUS = 'Rejected' THEN 1 ELSE 0 END) AS REJECTED,
        SUM(CASE WHEN pi.PRE_STATUS = 'Error' THEN 1 ELSE 0 end) AS ERROR,
        SUM(CASE WHEN pi.PRE_STATUS = 'Cleared' THEN 1 ELSE 0 END) AS CLEARED, 
        SUM(CASE WHEN pi.PRE_STATUS = 'Rejected' AND pi.CLEAR_PROCESS_DT IS NOT NULL THEN 1 ELSE 0 END) AS RETURNED,
        SUM(CASE WHEN pi.PRE_STATUS = 'Accepted' THEN pi.AMOUNT ELSE 0 END) AS ACCEPTEDAMOUNT,
        SUM(CASE WHEN pi.PRE_STATUS = 'Rejected' THEN pi.AMOUNT ELSE 0 END) AS REJECTEDAMOUNT,
        SUM(CASE WHEN pi.PRE_STATUS = 'Rejected' AND pi.CLEAR_PROCESS_DT IS NOT NULL THEN pi.AMOUNT ELSE 0 END) AS RETURNEDAMOUNT
    FROM PRE_ITM_DETAIL_VW pi 
    GROUP BY
         pi.CUSTOMER_GROUP, pi.CHANNEL
) v
GO

--IF EXISTS VIEW PRE_PRODUCT_SUMMARY_VW THEN DORP IT OR CREATE IT
IF EXISTS(SELECT 1 FROM sys.views WHERE name='PRE_PRODUCT_SUMMARY_VW')
DROP VIEW PRE_PRODUCT_SUMMARY_VW
GO

CREATE VIEW PRE_PRODUCT_SUMMARY_VW
AS
SELECT 
    pi.PRODUCT AS PRODUCT,
    pi.CUSTOMER_GROUP AS CUSTOMER_GROUP, 
    SUM(CASE WHEN pi.PRE_STATUS IN ('Accepted', 'Cleared', 'Clearing') OR pi.PRE_STATUS = 'Rejected' AND pi.PERSENT_DATE IS NOT NULL THEN 1 ELSE 0 END) AS PRESENTED,
    SUM(CASE WHEN pi.PRE_STATUS = 'Pending' THEN 1 ELSE 0 END) AS PENDING,
    SUM(CASE WHEN pi.PRE_STATUS = 'Accepted' THEN 1 ELSE 0 END) AS ACCEPTED,
    SUM(CASE WHEN pi.PRE_STATUS = 'Rejected' THEN 1 ELSE 0 END) AS REJECTED,
    SUM(CASE WHEN pi.PRE_STATUS = 'Error' THEN 1 ELSE 0 END) AS ERROR,
    SUM(CASE WHEN pi.PRE_STATUS = 'Cleared' THEN 1 ELSE 0 END) AS CLEARED, 
    SUM(CASE WHEN pi.PRE_STATUS = 'Rejected' AND pi.CLEAR_PROCESS_DT IS NOT NULL THEN 1 ELSE 0 END) AS RETURNED
FROM PRE_ITM_DETAIL_VW pi
GROUP BY
    pi.CUSTOMER_GROUP, pi.PRODUCT
GO

--IF EXISTS VIEW AUDIT_TRIAL_VW THEN DORP IT OR CREATE IT
IF EXISTS(SELECT 1 FROM sys.views WHERE name='AUDIT_TRIAL_VW')
DROP VIEW AUDIT_TRIAL_VW
GO

CREATE VIEW AUDIT_TRIAL_VW
AS
SELECT
    REPLACE(lg.ACCOUNT_NO, '-', '') as ACCOUNT_NO, 
    [dbo].[fn_GET_MOD_NAME](lg.MODULE) AS MODULE, 
    [dbo].[fn_GET_ACTIVITY_NAME](lg.ACTIVITY, lg.MODULE) AS ACTIVITY,
    [dbo].[fn_GET_ACT_STATUS](lg.STATUS) AS STATUS,
    lg.DETAILS, lg.MAKER_ID, lg.CHECKER_ID, 
    lg.MAINTENANCE_DATE AS MAINTENANCE_DATE
FROM
(
    SELECT 
      P_PAYEE_ACC AS ACCOUNT_NO, 
      MOD_ID AS MODULE, 
      ACT_COD AS ACTIVITY, 
      ACT_RESULT AS STATUS, 
      LOG_DTL AS DETAILS, 
      ADM_NM AS MAKER_ID, 
      NULL AS CHECKER_ID, 
      LOG_CREATE_AT AS MAINTENANCE_DATE
    FROM
    (
        (SELECT * FROM BOA_ADM_LOG_TBL WHERE MOD_ID = 108 AND ACT_COD = 5) boa_admin JOIN CHQ_PRE_ITM_TBL chq ON (chq.P_REF = boa_admin.OBJ_ID)
    )
    UNION ALL
    (
        SELECT 
            OBJ_ID AS ACCOUNT_NO, 
            MOD_ID AS MODULE, 
            ACT_COD AS ACTIVITY, 
            ACT_RESULT AS STATUS, 
            LOG_DTL AS DETAILS, 
            ADM_NM AS MAKER_ID, 
            NULL AS CHECKER_ID, 
            LOG_CREATE_AT AS MAINTENANCE_DATE 
        FROM 
            BOA_ADM_LOG_TBL 
        WHERE 
            MOD_ID = 107 AND ACT_COD IN (6, 12, 18)
    )
    UNION ALL
        SELECT 
            P_PAYEE_ACC AS ACCOUNT_NO, 
            MOD_ID AS MODULE, 
            MOD_ACT AS ACTIVITY, 
            case when CHK_ACT = 0 then 2 when CHK_ACT = 1 then 3 else 4 end AS STATUS, 
            MAK_DESC AS DETAILS, 
            MAK_ADM_NM AS Maker_ID, 
            CHK_ADM_NM AS Checker_ID, 
            CHK_HND_AT AS MAINTENANCE_DATE 
        FROM
          (
              (SELECT * FROM BOA_MAK_CHK_ARC_TBL WHERE MOD_ID = 108 AND MOD_ACT = 180) maker_checker join CHQ_PRE_ITM_TBL chq ON (chq.P_REF = maker_checker.OBJ_ID)
          )
    UNION ALL
    (
        SELECT 
            OBJ_ID as ACCOUNT_NO, 
            MOD_ID as MODULE, 
            MOD_ACT as ACTIVITY, 
            case when CHK_ACT = 0 then 2 when CHK_ACT = 1 then 3 else 4 end as STATUS, 
            MAK_DESC as DETAILS, 
            MAK_ADM_NM as Maker_ID, 
            CHK_ADM_NM as Checker_ID, 
            CHK_HND_AT as MAINTENANCE_DATE 
        FROM 
            BOA_MAK_CHK_ARC_TBL 
        WHERE 
            MOD_ID = 107 AND MOD_ACT in (170, 171, 172)
    )
) lg
GO

--IF EXISTS VIEW BOA_USR_TEMP_VW THEN DORP IT OR CREATE IT
IF EXISTS(SELECT 1 FROM sys.views WHERE name='BOA_USR_TEMP_VW')
DROP VIEW BOA_USR_TEMP_VW
GO

CREATE VIEW BOA_USR_TEMP_VW 
AS
SELECT 
    adm.ADM_ID USR_ID, 
    adm.ADM_NM USER_NAME, 
    adm.ADM_EMAIL EMAIL, 
    adm.ADM_STS STATUS, 
    grp.GRP_NM AUTHORITY, 
    adm.ADM_CREATE_AT CREATE_DATE, 
    adm.ADM_UPD_AT UPDATE_DATE 
FROM 
    BOA_ADM_TBL adm 
JOIN 
    BOA_ADM_GRP_MAP_TBL mapt 
ON 
    (adm.ADM_ID=mapt.ADM_ID) 
JOIN 
    BOA_GRP_TBL grp 
ON 
    (mapt.GRP_ID=grp.GRP_ID)
WHERE
    adm.ADM_STS <> 9
GO

--SELECT * FROM BOA_USR_TEMP_VW GO
--IF EXISTS VIEW BOA_USR_VW THEN DORP IT OR CREATE IT
IF EXISTS(SELECT 1 FROM sys.views WHERE name='BOA_USR_VW')
    DROP VIEW BOA_USR_VW
GO

CREATE VIEW BOA_USR_VW
AS
SELECT
     B.USR_ID,
    B.USER_NAME, 
    B.EMAIL, 
    [dbo].[fn_GET_STATUS](B.STATUS) STATUS, 
    B.CREATE_DATE, 
    B.UPDATE_DATE, 
    LEFT(AuthList,LEN(AuthList)-1) as AUTHORITY 
FROM (
    SELECT
        USR_ID, 
        USER_NAME,
        EMAIL,
        STATUS,
        CREATE_DATE,
        UPDATE_DATE,
        (SELECT AUTHORITY+',' FROM BOA_USR_TEMP_VW WHERE USER_NAME=A.USER_NAME FOR XML PATH('')) AS AuthList
    FROM 
        BOA_USR_TEMP_VW A 
    GROUP BY 
        USR_ID,USER_NAME,EMAIL,STATUS,CREATE_DATE,UPDATE_DATE
) B
GO

--SELECT * FROM BOA_USR_VW GO

--IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fnTest]') AND xtype IN (N'FN', N'IF', N'TF')) 
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='fn' AND NAME='fnTest')
    DROP FUNCTION [dbo].[fnTest]
GO

CREATE FUNCTION fnTest(@u int ) 
RETURNS varchar(60) 
AS 
BEGIN 
DECLARE @uu varchar(60) 
SET 
  @uu=CASE 
      WHEN @u=1 THEN '中国历史111' 
      WHEN @u=0 THEN '中国历史000'
      ELSE '中国历史' 
      END RETURN @uu 
END
GO

SELECT 
    [dbo].[fnTest](stuId) as a,
    stuName as b
FROM 
    STUDENT 
GO

INSERT INTO student VALUES (1, 1, '张三', '爬山') GO
INSERT INTO student VALUES (2, 1, '张三', '爬山') GO
INSERT INTO student VALUES (3, 2, '李四', '打球') GO
INSERT INTO student VALUES (4, 2, '李四', '打球') GO
INSERT INTO student VALUES (5, 3, '王五', '游泳') GO
INSERT INTO student VALUES (6, 3, '王五', '游泳') GO

SELECT * FROM student GO

SELECT 
    B.stuName,
    LEFT(StuList,LEN(StuList)-1) as hobby 
FROM (
    SELECT 
        stuName,
        (SELECT hobby+',' FROM student WHERE stuName=A.stuName FOR XML PATH('')) AS StuList
    FROM 
       student A 
    GROUP BY 
        stuName
) B
GO