· 8 years ago · Sep 02, 2018, 06:42 AM
1Converting PlSql Function into Tsql
2CREATE OR REPLACE function yonetici_kontrol_musteri (p_ID_MUSTERI_SIRKET in number, p_ID_YONETICI in number)
3return number
4is
5v_unvan number;
6v_yonetici number;
7v_tmp_unvan number;
8v_tmp_yonetici number;
9v_result number;
10
11begin
12
13v_result:=-1;
14
15
16SELECT id_unvan INTO v_unvan FROM lu_yonetici WHERE id_yonetici=p_ID_YONETICI;
17
18
19for c in ( SELECT NVL (b.id_mufettis, 0) id_mufettis, b.id_sef
20 FROM cr_rut_musteri c, lu_bayi_temsilci b
21 WHERE c.id_musteri_sirket = p_id_musteri_sirket
22 AND c.id_temsilci = b.id_temsilci
23 AND c.valid = 1
24 AND b.valid = 1
25 AND c.aktif = 1
26 AND b.aktif = 1
27 )
28loop
29
30 CASE v_unvan
31 WHEN 1 THEN
32 if c.id_mufettis = p_ID_YONETICI then
33 v_result:=1;
34 else
35 v_result:=0;
36 end if;
37 WHEN 2 THEN -- satis sefi
38 if c.id_sef = p_ID_YONETICI then
39 v_result:=1;
40 else
41 v_result:=0;
42 end if;
43 ELSE
44 v_yonetici:=c.id_sef;
45
46 loop
47 SELECT uy.id_unvan, uy.id_yonetici INTO v_tmp_unvan, v_tmp_yonetici
48 FROM lu_yonetici y, lu_yonetici uy
49 WHERE y.id_ust_yonetici=uy.id_yonetici
50 AND y.id_yonetici=v_yonetici;
51
52 if v_tmp_unvan=v_unvan then
53 if v_tmp_yonetici=p_ID_YONETICI then
54 v_result:=1;
55 else
56 v_result:=0;
57 end if;
58 else
59 v_yonetici:=v_tmp_yonetici;
60 end if;
61
62 exit when v_result=1 or v_tmp_unvan>=v_unvan;
63 end loop;
64 END CASE;
65 exit when v_result=1;
66
67end loop;
68
69return v_result;
70exception
71when others then
72return 0;
73end;
74
75--SWISSQL DROP SCRIPTS
76If Exists ( SELECT name
77 FROM sysobjects
78 WHERE name = 'yonetici_kontrol_musteri'
79 AND type = 'FN')
80 DROP FUNCTION yonetici_kontrol_musteri
81GO
82CREATE FUNCTION yonetici_kontrol_musteri
83(
84 @p_ID_MUSTERI_SIRKET FLOAT ,
85 @p_ID_YONETICI FLOAT
86)
87RETURNS FLOAT
88AS
89 BEGIN
90
91
92 DECLARE @adv_error INT
93
94 DECLARE @v_unvan FLOAT
95 DECLARE @v_yonetici FLOAT
96 DECLARE @v_tmp_unvan FLOAT
97 DECLARE @v_tmp_yonetici FLOAT
98 DECLARE @v_result FLOAT
99 SELECT @v_result = - 1
100 SELECT @v_unvan = id_unvan
101 FROM lu_yonetici
102 WHERE id_yonetici = @p_ID_YONETICI
103
104
105 SELECT @adv_error = @@ERROR
106 IF @adv_error != 0
107 BEGIN
108 GOTO Exception1
109 END
110
111
112
113 DECLARE cursor_for_inline_select1 CURSOR LOCAL FOR
114 SELECT
115 ISNULL(b.id_mufettis, 0) id_mufettis,
116 b.id_sef
117 FROM cr_rut_musteri c,
118 lu_bayi_temsilci b
119 WHERE c.id_musteri_sirket = @p_id_musteri_sirket
120 AND c.id_temsilci = b.id_temsilci
121 AND c.valid = 1
122 AND b.valid = 1
123 AND c.aktif = 1
124 AND b.aktif = 1
125
126 OPEN cursor_for_inline_select1
127
128 FETCH NEXT FROM cursor_for_inline_select1 INTO
129 /* SwisSQL (Oracle To SQL Server) : Table Information is not found in METADATA for the CURSOR used by the record-index */
130
131 WHILE (@@FETCH_STATUS <> -1)
132
133 BEGIN
134
135 IF @v_unvan = 1
136 BEGIN
137 IF /* SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ c.id_mufettis = @p_ID_YONETICI
138 BEGIN
139 SELECT @v_result = 1
140 END
141 ELSE
142 BEGIN
143 SELECT @v_result = 0
144 END
145 END
146 ELSE
147 IF @v_unvan = 2
148 BEGIN
149 -- satis sefi
150
151 IF /* SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ c.id_sef = @p_ID_YONETICI
152 BEGIN
153 SELECT @v_result = 1
154 END
155 ELSE
156 BEGIN
157 SELECT @v_result = 0
158 END
159 END
160 ELSE
161 BEGIN
162 SELECT @v_yonetici = /* SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ c.id_sef
163 DECLARE @count INT
164 SELECT @count = 1
165 WHILE (0 = 0)
166 BEGIN --(
167 SELECT
168 @v_tmp_unvan = uy.id_unvan,
169 @v_tmp_yonetici = uy.id_yonetici
170 FROM lu_yonetici y,
171 lu_yonetici uy
172 WHERE y.id_ust_yonetici = uy.id_yonetici
173 AND y.id_yonetici = @v_yonetici
174
175
176 SELECT @adv_error = @@ERROR
177 IF @adv_error != 0
178 BEGIN
179 GOTO Exception1
180 END
181
182 IF @v_tmp_unvan = @v_unvan
183 BEGIN
184 IF @v_tmp_yonetici = @p_ID_YONETICI
185 BEGIN
186 SELECT @v_result = 1
187 END
188 ELSE
189 BEGIN
190 SELECT @v_result = 0
191 END
192
193 END
194 ELSE
195 BEGIN
196 SELECT @v_yonetici = @v_tmp_yonetici
197 END
198
199 IF @v_result = 1 or @v_tmp_unvan >= @v_unvan
200 BREAK
201
202 SELECT @count=@count +1
203 END --)
204
205 END
206 IF @v_result = 1
207 BREAK
208
209
210
211 FETCH NEXT FROM cursor_for_inline_select1 INTO
212 /* SwisSQL (Oracle To SQL Server) : Table Information is not found in METADATA for the CURSOR used by the record-index */
213 END
214 CLOSE cursor_for_inline_select1
215 DEALLOCATE cursor_for_inline_select1
216
217
218 DEALLOCATE cursor_for_inline_select1
219 return @v_result
220 GOTO ExitLabel1
221 Exception1:
222
223 BEGIN
224
225 DEALLOCATE cursor_for_inline_select1
226 return 0
227 /* SwisSQL (Oracle To SQL Server) : Manual Intervention to verify Exception is required */
228 END
229 ExitLabel1:
230
231 return @v_result
232
233 END
234
235
236GO
237 ########################################################################################################