常用SQL语句3（MSSQL）

--IF EXISTS FUNCTION fn_GET_PRESENT_STS THEN DORP IT OR CREATE IT 2 IF EXISTS (SELECT * FROM sysobjects WHERE xtype=‘fn‘ AND NAME=‘fn_GET_PRESENT_STS‘) 3 DROP FUNCTION [dbo].[fn_GET_PRESENT_STS] 4 GO 5 6 CREATE FUNCTION fn_GET_PRESENT_STS(@p_status INT) 7 RETURNS VARCHAR(60) 8 AS 9 BEGIN 10 DECLARE @l_rtn varchar(60) 11 SET 12 @l_rtn=CASE 13 WHEN @p_status=0 THEN ‘Rejected‘ 14 WHEN @p_status=1 THEN ‘Accepted‘ 15 WHEN @p_status=2 THEN ‘Pending‘ 16 WHEN @p_status=3 THEN ‘Submitting‘ 17 WHEN @p_status=4 THEN ‘Error‘ 18 WHEN @p_status=5 THEN ‘Cleared‘ 19 WHEN @p_status=6 THEN ‘Clearing‘ 20 ELSE ‘N/A‘ 21 END 22 RETURN @l_rtn 23 END 24 GO 25 26 --IF EXISTS FUNCTION fn_GET_ACC_VERIFY_STS THEN DORP IT OR CREATE IT 27 IF EXISTS (SELECT * FROM sysobjects WHERE xtype=‘fn‘ AND NAME=‘fn_GET_ACC_VERIFY_STS‘) 28 DROP FUNCTION [dbo].[fn_GET_ACC_VERIFY_STS] 29 GO 30 31 CREATE FUNCTION fn_GET_ACC_VERIFY_STS(@acc_status INT) 32 RETURNS VARCHAR(60) 33 AS 34 BEGIN 35 DECLARE @l_rtn varchar(60) 36 SET 37 @l_rtn=CASE 38 WHEN @acc_status=0 THEN ‘Mismatched‘ 39 WHEN @acc_status=1 THEN ‘Matched‘ 40 WHEN @acc_status=2 THEN ‘Undetermined‘ 41 WHEN @acc_status=3 THEN ‘Rejected‘ 42 WHEN @acc_status=4 THEN ‘Pending‘ 43 ELSE ‘N/A‘ 44 END 45 RETURN @l_rtn 46 END 47 GO 48 49 --IF EXISTS FUNCTION fn_GET_CRD_STS THEN DORP IT OR CREATE IT 50 IF EXISTS (SELECT * FROM sysobjects WHERE xtype=‘fn‘ AND NAME=‘fn_GET_CRD_STS‘) 51 DROP FUNCTION [dbo].[fn_GET_CRD_STS] 52 GO 53 54 CREATE FUNCTION fn_GET_CRD_STS(@crd_status INT) 55 RETURNS VARCHAR(60) 56 AS 57 BEGIN 58 DECLARE @l_rtn varchar(60) 59 SET 60 @l_rtn=CASE 61 WHEN @crd_status=0 THEN ‘Not Granted‘ 62 WHEN @crd_status=1 THEN ‘Granted‘ 63 WHEN @crd_status=2 THEN ‘Reverted‘ 64 WHEN @crd_status=3 THEN ‘Error‘ 65 WHEN @crd_status=4 THEN ‘Submitting‘ 66 ELSE ‘N/A‘ 67 END 68 RETURN @l_rtn 69 END 70 GO 71 72 --IF EXISTS FUNCTION fn_GET_CHANNEL THEN DORP IT OR CREATE IT 73 IF EXISTS (SELECT * FROM sysobjects WHERE xtype=‘fn‘ AND NAME=‘fn_GET_CHANNEL‘) 74 DROP FUNCTION [dbo].[fn_GET_CHANNEL] 75 GO 76 77 CREATE FUNCTION fn_GET_CHANNEL(@cust_1 VARCHAR(10), @p_tp INT ) 78 RETURNS VARCHAR(60) 79 AS 80 BEGIN 81 DECLARE @l_rtn varchar(60) 82 SET 83 @l_rtn=CASE 84 WHEN @cust_1=‘IB‘ THEN ‘iBanking‘ 85 WHEN @cust_1=‘FC‘ THEN ‘FCDB‘ 86 WHEN @cust_1=‘CW‘ THEN ‘Corp Website‘ 87 WHEN @cust_1 IS NULL AND @p_tp=2 THEN ‘HKICL Portal‘ 88 ELSE @cust_1 89 END 90 RETURN @l_rtn 91 END 92 GO 93 94 --IF EXISTS FUNCTION fn_GET_GROUP THEN DORP IT OR CREATE IT 95 IF EXISTS (SELECT * FROM sysobjects WHERE xtype=‘fn‘ AND NAME=‘fn_GET_GROUP‘) 96 DROP FUNCTION [dbo].[fn_GET_GROUP] 97 GO 98 99 CREATE FUNCTION fn_GET_GROUP(@cust_2 VARCHAR(20)) 100 RETURNS VARCHAR(60) 101 AS 102 BEGIN 103 DECLARE @l_rtn varchar(60) 104 SET 105 @l_rtn=CASE 106 WHEN @cust_2=‘PR‘ THEN ‘Personal‘ 107 WHEN @cust_2=‘BB‘ THEN ‘Business Banking‘ 108 WHEN @cust_2=‘CP‘ THEN ‘Corporate‘ 109 ELSE @cust_2 110 END 111 RETURN @l_rtn 112 END 113 GO 114 115 --IF EXISTS FUNCTION fn_GET_PRODUCT THEN DORP IT OR CREATE IT 116 IF EXISTS (SELECT * FROM sysobjects WHERE xtype=‘fn‘ AND NAME=‘fn_GET_PRODUCT‘) 117 DROP FUNCTION [dbo].[fn_GET_PRODUCT] 118 GO 119 120 CREATE FUNCTION fn_GET_PRODUCT(@cust_3 VARCHAR(20)) 121 RETURNS VARCHAR(60) 122 AS 123 BEGIN 124 DECLARE @l_rtn varchar(60) 125 SET 126 @l_rtn=CASE 127 WHEN @cust_3=‘CASA‘ THEN ‘CASA‘ 128 WHEN @cust_3=‘CC‘ THEN ‘Credit Card‘ 129 WHEN @cust_3=‘LOAN‘ THEN ‘Loans‘ 130 WHEN @cust_3=‘INSU‘ THEN ‘Insurance‘ 131 ELSE @cust_3 132 END 133 RETURN @l_rtn 134 END 135 GO 136 137 --IF EXISTS FUNCTION fn_GET_RETURN_REASON DORP IT OR CREATE IT 138 IF EXISTS (SELECT * FROM sysobjects WHERE xtype=‘fn‘ AND NAME=‘fn_GET_RETURN_REASON‘) 139 DROP FUNCTION [dbo].[fn_GET_RETURN_REASON] 140 GO 141 142 CREATE FUNCTION fn_GET_RETURN_REASON(@present_val INT) 143 RETURNS VARCHAR(100) 144 AS 145 BEGIN 146 DECLARE @l_rtn varchar(100) 147 SET 148 @l_rtn=CASE 149 WHEN @present_val=1 THEN ‘Refer To Drawer (‘ + cast(@present_val as varchar(50)) + ‘)‘ 150 WHEN @present_val=3 THEN ‘Drawn against uncollected funds (‘ +cast(@present_val as varchar(50)) + ‘)‘ 151 WHEN @present_val=4 THEN ‘Payer Account Closed (‘ + cast(@present_val as varchar(50)) + ‘)‘ 152 WHEN @present_val=6 THEN ‘e-Cheque Is Out-of-date (‘ + cast(@present_val as varchar(50)) + ‘)‘ 153 WHEN @present_val=7 THEN ‘Post-dated e-Cheque (‘ + cast(@present_val as varchar(50)) + ‘)‘ 154 WHEN @present_val=8 THEN ‘Payment Countermanded By The Drawer (‘ + cast(@present_val as varchar(50)) + ‘)‘ 155 WHEN @present_val=26 THEN ‘Not Drawn On This Bank (‘ + cast(@present_val as varchar(50)) + ‘)‘ 156 WHEN @present_val=27 THEN ‘Others (‘ + cast(@present_val as varchar(50)) + ‘)‘ 157 WHEN @present_val=28 THEN ‘Duplicated Presentment (‘ + cast(@present_val as varchar(50)) + ‘)‘ 158 WHEN @present_val=29 THEN ‘Invalid e-Cheque (‘ + cast(@present_val as varchar(50)) + ‘)‘ 159 WHEN @present_val=30 THEN ‘e-Cheque Already Drawn (‘ + cast(@present_val as varchar(50)) + ‘)‘ 160 WHEN @present_val=51 THEN ‘Invalid Payee Account Or Unsupported Currency (‘ +cast(@present_val as varchar(50)) + ‘)‘ 161 WHEN @present_val=52 THEN ‘Payee Account Not Exists (‘ + cast(@present_val as varchar(50)) + ‘)‘ 162 WHEN @present_val=53 THEN ‘Invalid Payee (‘ + cast(@present_val as varchar(50)) + ‘)‘ 163 WHEN @present_val=54 THEN ‘Invalid Payer Branch (‘ + cast(@present_val as varchar(50)) + ‘)‘ 164 WHEN @present_val=55 THEN ‘Invalid Payer Account (‘ + cast(@present_val as varchar(50)) + ‘)‘ 165 WHEN @present_val=56 THEN ‘Invalid Payee Bank (‘ + cast(@present_val as varchar(50)) + ‘)‘ 166 WHEN @present_val=57 THEN ‘Invalid Payer Bank (‘ + cast(@present_val as varchar(50)) + ‘)‘ 167 WHEN @present_val=58 THEN ‘e-Cheque Issued In This Currency Is Not Accepted By Payee Account (‘ + cast(@present_val as varchar(50)) + ‘)‘ 168 WHEN @present_val=61 THEN ‘Invalid File Type (‘ + cast(@present_val as varchar(50)) + ‘)‘ 169 WHEN @present_val=62 THEN ‘File Size Exceeded The Limits (‘ + cast(@present_val as varchar(50)) + ‘)‘ 170 WHEN @present_val=63 THEN ‘Might Be A Fake e-Cheque (‘ + cast(@present_val as varchar(50)) + ‘)‘ 171 WHEN @present_val=64 THEN ‘Invalid e-Cheque (‘ + cast(@present_val as varchar(50)) + ‘)‘ 172 WHEN @present_val=65 THEN ‘Unsupported Currency (‘ + cast(@present_val as varchar(50)) + ‘)‘ 173 WHEN @present_val=71 THEN ‘Presentment Already Accepted (‘ + cast(@present_val as varchar(50)) + ‘)‘ 174 WHEN @present_val=72 THEN ‘Duplicated Presentment Reference (‘ + cast(@present_val as varchar(50)) + ‘)‘ 175 WHEN @present_val=73 THEN ‘Invalid Presentment Reference (‘ + cast(@present_val as varchar(50)) + ‘)‘ 176 WHEN @present_val=74 THEN ‘Presentment Return Already Accepted (‘ + cast(@present_val as varchar(50)) + ‘)‘ 177 WHEN @present_val=75 THEN ‘Presentment Not Exists Or Not Accepted Previously (‘ + cast(@present_val as varchar(50)) + ‘)‘ 178 WHEN @present_val=76 THEN ‘Presentment Already Submitted To Clearing (‘ + cast(@present_val as varchar(50)) + ‘)‘ 179 WHEN @present_val=99 THEN ‘Please Contact Bank (‘ + cast(@present_val as varchar(50)) + ‘)‘ 180 ELSE ‘null‘ 181 END 182 RETURN @l_rtn 183 END 184 GO 185 186 --IF EXISTS FUNCTION fn_GET_ACTIVITY_NAME DORP IT OR CREATE IT 187 IF EXISTS (SELECT * FROM sysobjects WHERE xtype=‘fn‘ AND NAME=‘fn_GET_ACTIVITY_NAME‘) 188 DROP FUNCTION [dbo].[fn_GET_ACTIVITY_NAME] 189 GO 190 191 CREATE FUNCTION fn_GET_ACTIVITY_NAME(@mod_act INT, @mod_id INT) 192 RETURNS VARCHAR(30) 193 AS 194 BEGIN 195 DECLARE @l_rtn varchar(30) 196 SET 197 @l_rtn=CASE 198 WHEN @mod_act=5 AND @mod_id <> 107 THEN ‘Edit‘ 199 WHEN @mod_act=5 AND @mod_id=107 THEN ‘Presentment Update‘ 200 WHEN @mod_act=6 THEN ‘Remove‘