常用SQL语句3（MSSQL）
时间：2021-07-01 10:21:17
帮助过：11人阅读
--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‘
