980行存储过程

小日本的要求还真诡异，不知道服务器抗得住抗不住的

  1CREATE PROCEDURE CS_BUSINESS_PROGRESS(
  2    @STRWORKERNO VARCHAR(10),
  3    @STRPRINTNO VARCHAR(10)
  4) AS 
  5
  6DECLARE @WORKERNO VARCHAR(10)
  7    DECLARE @PRINTNO VARCHAR(10)
  8SET @WORKERNO=@STRWORKERNO
  9SET @PRINTNO=@STRPRINTNO
 10
 11    DECLARE @LOOKUPVALUE1 VARCHAR(3)
 12    DECLARE @@LOOKUPVALUE2 VARCHAR(3)
 13    DECLARE @@CUSTOMERCODE VARCHAR(12)
 14    DECLARE @EMPLOYEECODE VARCHAR(6)
 15    DECLARE @@CUSTOMERS_NAME VARCHAR(50)
 16    DECLARE @@FIRST_VISIT_DATE VARCHAR(10)
 17    DECLARE @@SOURCE_NAME VARCHAR(60)
 18    DECLARE @@BUILD_TYPE_NAME VARCHAR(60)
 19    DECLARE @@HOUSE_TYPE VARCHAR(20)
 20    DECLARE @@SPECIALLY_MONEY MONEY
 21    DECLARE @@FINANCE_RESERVE_DATE VARCHAR(10)
 22    DECLARE @@START_WORK_DATE VARCHAR(10)
 23    DECLARE @@APARTMENT_HOPE_DATE VARCHAR(10)
 24    DECLARE @@EUROPEAN_JAPANESE_STYLE VARCHAR(10)
 25    DECLARE @@BUILDPLACE_ADDRESS1 VARCHAR(300)
 26    DECLARE @@BUILDPLACE_ADDRESS2 VARCHAR(300)
 27    DECLARE @SCHEDULED_DATE DATETIME
 28    DECLARE @RESULTS_DATE DATETIME
 29    DECLARE @@SOIL_PRESENCE VARCHAR(10)
 30    DECLARE @RANK_NAME_TYPE1 VARCHAR(10)
 31    DECLARE @SELECTION_ADDRESS1 VARCHAR(300)
 32    DECLARE @EMPLOYEE_COMMENT VARCHAR(100)
 33    DECLARE @ACTION_ITEMS_NAME VARCHAR(100)
 34    DECLARE @SOIL_PRESENCENAME VARCHAR(100)
 35    DECLARE @HOUSE_TYPENAME VARCHAR(100)
 36    DECLARE @INTNUM  INT
 37    DECLARE @FLAG VARCHAR(1)
 38    DECLARE @FLAG1 VARCHAR(1)
 39    DECLARE @FLAG2 VARCHAR(1)
 40    DECLARE @FLAG3 VARCHAR(1)
 41    DECLARE @FLAG4 VARCHAR(1)
 42    DECLARE @FLAG5 VARCHAR(1)
 43    DECLARE @ACTION_DATE1 DATETIME
 44    DECLARE @ACTION_DATE2 DATETIME
 45    DECLARE @ACTION_DATE3 DATETIME
 46    DECLARE @ACTION_DATE4 DATETIME
 47    DECLARE @ACTION_DATE5 DATETIME
 48    DECLARE @STRSOIL_PRESENCENAME VARCHAR(100)
 49    DECLARE @STRHOUSE_TYPENAME VARCHAR(100)
 50    DECLARE @@RANK_NAME_TYPE1 VARCHAR(10)
 51    DECLARE @@SELECTION_ADDRESS1 VARCHAR(300)
 52    DECLARE @STREMPLOYEE_COMMENT VARCHAR(400)
 53    DECLARE @STRACTION_ITEMS_NAME  VARCHAR(100)
 54    DECLARE @STRSELECTION_ADDRESS1 VARCHAR(300)
 55    SELECT @INTNUM=0
 56    DECLARE @RESULTS_DATE1  DATETIME
 57    declare @ACTION_ITEMS_NAME1 varchar(300)
 58    DECLARE @DATENUM INT
 59    SELECT @DATENUM=0
 60
 61SELECT @LOOKUPVALUE1=LOOKUP_VALUE    --B-1
 62    FROM CM_LOOKUP
 63    WHERE LOOKUP_TYPE  like '%' +'営業進捗' + '%'
 64    AND LOOKUP_CODE='10'
 65
 66DECLARE CUST_Cursor1 CURSOR FOR
 67    SELECT distinct CUSTOMERS_CODE,     --A-1
 68        CUSTOMERS_NAME,
 69        FIRST_VISIT_DATE,
 70        SOURCE_NAME,
 71        BUILD_TYPE_NAME,
 72        HOUSE_TYPE,
 73        SPECIALLY_MONEY,
 74        FINANCE_RESERVE_DATE,
 75        START_WORK_DATE,
 76        APARTMENT_HOPE_DATE,
 77        EUROPEAN_JAPANESE_STYLE,
 78        BUILDPLACE_ADDRESS1,
 79        BUILDPLACE_ADDRESS2
 80    FROM CV_CUSTOMERS
 81    WHERE 
 82        EMPLOYEE_CODE=@PRINTNO
 83    AND 
 84        CONDITIONS_CODE=@LOOKUPVALUE1
 85
 86
 87DECLARE CUST_Cursor2 CURSOR FOR
 88    SELECT LOOKUP_VALUE
 89    FROM CM_LOOKUP
 90    WHERE LOOKUP_TYPE like '%' +'営業進捗' + '%'
 91    AND LOOKUP_CODE='11'
 92    
 93    OPEN CUST_Cursor1
 94
 95FETCH NEXT FROM CUST_Cursor1 
 96    INTO
 97    @@CUSTOMERCODE,    --A-1
 98    @@CUSTOMERS_NAME,
 99    @@FIRST_VISIT_DATE,
100    @@SOURCE_NAME,
101    @@BUILD_TYPE_NAME,
102    @@HOUSE_TYPE,
103    @@SPECIALLY_MONEY,
104    @@FINANCE_RESERVE_DATE,
105    @@START_WORK_DATE,
106    @@APARTMENT_HOPE_DATE,
107    @@EUROPEAN_JAPANESE_STYLE,
108    @@BUILDPLACE_ADDRESS1,
109    @@BUILDPLACE_ADDRESS2
110
111    WHILE @@FETCH_STATUS=0
112    begin
113
114    OPEN CUST_Cursor2
115    
116    FETCH NEXT FROM CUST_Cursor2
117    INTO @@LOOKUPVALUE2       --B-2
118SET @INTNUM=0
119WHILE @@FETCH_STATUS=0
120--SELECT @SCHEDULED_DATE=''
121begin
122    SET @INTNUM= @INTNUM+1
123    SELECT @SCHEDULED_DATE=SCHEDULED_DATE,
124        @RESULTS_DATE=RESULTS_DATE
125    FROM 
126        CV_ACTIONS
127    WHERE 
128        ACTION_ITEMS_CODE=@@LOOKUPVALUE2
129    AND 
130        ACTION_RECORD_TYPE='1'
131    AND 
132        CUSTOMERS_CODE=@@CUSTOMERCODE
133
134    IF  @RESULTS_DATE IS NOT NULL and @RESULTS_DATE<>''
135        BEGIN
136        SELECT @SCHEDULED_DATE=@RESULTS_DATE
137        SELECT @FLAG=1
138        END
139    ELSE IF @SCHEDULED_DATE IS NOT NULL and @SCHEDULED_DATE<>''
140    BEGIN
141    SELECT @SCHEDULED_DATE=@SCHEDULED_DATE
142    SELECT @FLAG=0
143END
144    ELSE
145BEGIN
146        SELECT @SCHEDULED_DATE=''
147        SELECT @FLAG=0
148END
149
150    IF @INTNUM=1 
151BEGIN
152        SELECT @ACTION_DATE1=CONVERT(VARCHAR(10),@SCHEDULED_DATE,111)
153        SELECT @FLAG1=@FLAG
154        select @SCHEDULED_DATE=''
155        if @ACTION_DATE1='1900/01/01'
156        select @ACTION_DATE1=null
157        
158END
159    ELSE IF @INTNUM=2
160BEGIN
161        SELECT @ACTION_DATE2=CONVERT(VARCHAR(10),@SCHEDULED_DATE,111)
162        SELECT @FLAG2=@FLAG
163        select @SCHEDULED_DATE=''
164
165        if @ACTION_DATE2='1900/01/01'
166        select @ACTION_DATE2=null
167END
168    ELSE IF @INTNUM=3
169BEGIN
170        SELECT @ACTION_DATE3=CONVERT(VARCHAR(10),@SCHEDULED_DATE,111)
171        SELECT @FLAG3=@FLAG
172        select @SCHEDULED_DATE=''
173        if @ACTION_DATE3='1900/01/01'
174        select @ACTION_DATE3=null
175END
176    ELSE IF @INTNUM=4
177BEGIN
178        SET  @ACTION_DATE4=CONVERT(VARCHAR(10),@SCHEDULED_DATE,111)
179        SET @FLAG4=@FLAG
180        select @SCHEDULED_DATE=''
181        if @ACTION_DATE4='1900/01/01'
182        select @ACTION_DATE4=null
183END
184    ELSE IF @INTNUM=5
185    BEGIN
186        SET @ACTION_DATE5=CONVERT(VARCHAR(10),@SCHEDULED_DATE,111)
187        SET @FLAG5=@FLAG
188        select @SCHEDULED_DATE=''
189        if @ACTION_DATE5='1900/01/01'
190        select @ACTION_DATE5=null
191END
192    FETCH NEXT FROM CUST_Cursor2
193    INTO @@LOOKUPVALUE2       --B-2
194end
195    CLOSE  CUST_Cursor2
196
197    IF @@EUROPEAN_JAPANESE_STYLE='1'
198          SELECT  @STRSOIL_PRESENCENAME='有り'
199    ELSE IF @@EUROPEAN_JAPANESE_STYLE='2'
200               SELECT  @STRSOIL_PRESENCENAME='無し'
201    ELSE IF @@EUROPEAN_JAPANESE_STYLE='3'
202         SELECT @STRSOIL_PRESENCENAME='不明'
203
204
205
206    IF  @@HOUSE_TYPE='1'        
207        SELECT @STRHOUSE_TYPENAME='木造'        
208    ELSE IF @@HOUSE_TYPE='2'
209        SELECT @STRHOUSE_TYPENAME='ＲＣ造'
210    ELSE IF @@HOUSE_TYPE='3'
211        SELECT @STRHOUSE_TYPENAME='Ｓ造'
212    ELSE IF @@HOUSE_TYPE='4'
213        SELECT @STRHOUSE_TYPENAME='テクノ'
214    ELSE  IF @@HOUSE_TYPE='5'
215        SELECT @STRHOUSE_TYPENAME='その他'
216
217IF @WORKERNO IS NOT  NULL AND @PRINTNO IS NOT  NULL AND @@CUSTOMERCODE IS NOT  NULL AND @LOOKUPVALUE1 IS NOT  NULL
218BEGIN    
219INSERT INTO CW_BUSINESS_PROGRESS
220            (LOGIN_EMPLOYEE_CODE,
221            EMPLOYEE_CODE,
222            CUSTOMERS_CODE,
223            CONDITIONS_CODE,
224            DATA_FLAG,
225            CUSTOMERS_NAME,
226            FIRST_VISIT_DATE,
227            SOURCE_NAME,
228            BUILD_TYPE_NAME,
229            HOUSE_TYPE,
230            SPECIALLY_MONEY,
231            FINANCE_RESERVE_DATE,
232            START_WORK_DATE,
233            APARTMENT_HOPE_DATE,
234            EUROPEAN_JAPANESE_STYLE,
235            BUILDPLACE_ADDRESS,
236            ACTION_ITEMS1_DATE,
237            ACTION_ITEMS2_DATE,
238            ACTION_ITEMS3_DATE,
239            ACTION_ITEMS4_DATE,
240            ACTION_ITEMS5_DATE,
241            ACTION_ITEMS1_FLAG,
242            ACTION_ITEMS2_FLAG,
243            ACTION_ITEMS3_FLAG,
244            ACTION_ITEMS4_FLAG,
245            ACTION_ITEMS5_FLAG)
246    VALUES (@WORKERNO,
247            @PRINTNO,
248            @@CUSTOMERCODE,
249            @LOOKUPVALUE1,
250            '1',
251            @@CUSTOMERS_NAME,
252            CONVERT(VARCHAR(10),@@FIRST_VISIT_DATE,111),
253            @@SOURCE_NAME,
254            @@BUILD_TYPE_NAME,
255            @STRHOUSE_TYPENAME,
256            @@SPECIALLY_MONEY,
257            CONVERT(VARCHAR(10),@@FINANCE_RESERVE_DATE,111),
258            CONVERT(VARCHAR(10),@@START_WORK_DATE,111),
259            CONVERT(VARCHAR(10),@@APARTMENT_HOPE_DATE,111),
260            @STRSOIL_PRESENCENAME,
261            @@BUILDPLACE_ADDRESS1+@@BUILDPLACE_ADDRESS2,
262            CONVERT(VARCHAR(10),@ACTION_DATE1,111),
263            CONVERT(VARCHAR(10),@ACTION_DATE2,111),
264            CONVERT(VARCHAR(10),@ACTION_DATE3,111),
265            CONVERT(VARCHAR(10),@ACTION_DATE4,111),
266            CONVERT(VARCHAR(10),@ACTION_DATE5,111),
267            @FLAG1,
268            @FLAG2,
269            @FLAG3,
270            @FLAG4,
271            @FLAG5)
272END
273SELECT @STRHOUSE_TYPENAME=''
274SELECT @STRSOIL_PRESENCENAME=''
275SELECT  @FLAG1=''
276SELECT @FLAG2=''
277SELECT @FLAG3=''
278SELECT @FLAG4=''
279SELECT @FLAG5=''
280SELECT @FLAG=''
281SELECT @SCHEDULED_DATE=''
282--select @SCHEDULED_DATE=''
283FETCH NEXT FROM CUST_Cursor1 
284    INTO
285    @@CUSTOMERCODE,    --A-1
286    @@CUSTOMERS_NAME,
287    @@FIRST_VISIT_DATE,
288    @@SOURCE_NAME,
289    @@BUILD_TYPE_NAME,
290    @@HOUSE_TYPE,
291    @@SPECIALLY_MONEY,
292    @@FINANCE_RESERVE_DATE,
293    @@START_WORK_DATE,
294    @@APARTMENT_HOPE_DATE,
295    @@EUROPEAN_JAPANESE_STYLE,
296    @@BUILDPLACE_ADDRESS1,
297    @@BUILDPLACE_ADDRESS2
298
299    end
300
301    CLOSE  CUST_Cursor1
302    DEALLOCATE CUST_Cursor1
303        DEALLOCATE CUST_Cursor2
304
305    
306
307    SELECT @INTNUM=0
308SELECT @LOOKUPVALUE1=LOOKUP_VALUE    --B-1
309    FROM CM_LOOKUP
310    WHERE LOOKUP_TYPE  like '%' +'営業進捗' + '%'
311    AND LOOKUP_CODE='20'
312
313DECLARE CUST_Cursor3 CURSOR FOR