|
nvakeel
Yak Posting Veteran
52 Posts |
 Posted - 2008-02-04 : 15:38:49
|
| I have a SP which uses this cursor. The table has more than 2 lakh rows. Using cursors is a bad option as far I know. can you help me DECLARE CR1 CURSOR FOR SELECT JOB_INST_ID , ADV_NUM , SORT_CODE , SYS , LOC , num , F_NAME , LNAME , INIT , MISC_ADD , ADDRESSX , CTY_ST , ZIPCD , TMP_PRNJ250M2_ADV_TRAN_ID FROM MPAS_TMP.DBO.TMP_PRNJ250M_ADV_INTRAN WHERE JOB_INST_ID = @JOB_INST_ID OPEN CR1 FETCH NEXT FROM CR1 INTO @JOB_INST_ID , @ADV_NUM , @SORT_CODE , @SYS , @LOC , @num , @F_NAME , @LNAME , @INIT , @MISC_ADD , @ADDRESSX , @CTY_ST , @ZIPCD , @INTRAN_ID WHILE( @@FETCH_STATUS =0) BEGIN------------------CURSOR -------SET THE VALUE OF TMP_ADV_NUM---------------- SET @TMP_ADV_NUM = @ADV_NUM ----------SETTING THE VALUES OF TMP_TOT_ADVS AND TOT_ADVS------ print 'INSIDE CORSOR TEMP ADV NUM ' + @TMP_ADV_NUM + ' , NUM : ' + @num IF ((upper(@CODEX)!='R' )OR (upper(@CODEX) ='R' AND CONVERT(INT,@RES_ADV_NUM) < CONVERT(INT,@TMP_ADV_NUM)) OR (upper(@CODEX) ='R' AND CONVERT(INT,@END_ADV_NUM) > CONVERT(INT,@TMP_ADV_NUM)) ) BEGIN if LTRIM(RTRIM(@SORT_CODE)) NOT IN ('07','08','7','8') begin SET @TMP_TOT_ADVS = CONVERT(BIGINT,@TMP_TOT_ADVS) + 1 SET @TMP_END_ADV_NUM = @TMP_END_ADV_NUM + 1 end END ELSE IF(upper(@CODEX)='R') BEGIN --------------------7 SET @TMP_TOT_ADVS = CONVERT(BIGINT,@TMP_TOT_ADVS) + 1 IF LTRIM(RTRIM(@SORT_CODE)) NOT IN ('07','08','7','8') BEGIN SET @TMP_TOT_ADVS = CONVERT(BIGINT,@TMP_TOT_ADVS) + 1 SET @TMP_END_ADV_NUM = @TMP_END_ADV_NUM + 1 END END --------------------7 -----------UPDATE THE VALUE OF NEW_ADV_NUM IN TMP_PRNJ250M_ADV_INTRAN --------- ---creating index on 'TMP_PRNJ250M_ADV_INTRAN' table changed by Diya UPDATE MPAS_TMP.DBO.TMP_PRNJ250M_ADV_INTRAN SET NEW_ADV_NUM = @TMP_END_ADV_NUM WHERE JOB_INST_ID = @JOB_INST_ID AND TMP_PRNJ250M2_ADV_TRAN_ID = @INTRAN_ID PRINT 'END PROCESS TIME FOR NEW ADV NUM INTO TABLE'+ CONVERT(VARCHAR(32), CURRENT_TIMESTAMP, 109) ------INSERT RECORD IN ZIP REPORT WHEN SSN>0 AND ZIP_COD=''-------- IF(CONVERT(BIGINT,@num) > 0-- AND LTRIM(RTRIM(@ZIPCD)) = '' )-- BEGIN-- INSERT INTO MPAS_TMP.dbo.TMP_PRNJ250M_ZIPRPT-- SELECT @JOB_INST_ID , @SYS , @num , @LOC , -- @LNAME , @F_NAME , @INIT , @MISC_ADD , -- @ADDRESSX , @CTY_ST , @ZIPCD -- END FETCH NEXT FROM CR1 INTO @JOB_INST_ID , @ADV_NUM , @SORT_CODE , @SYS , @LOC , @num, @F_NAME , @LNAME , @INIT , @MISC_ADD , @ADDRESSX , @CTY_ST , @ZIPCD , @INTRAN_ID END---------------------CURSOR CLOSE CR1 DEALLOCATE CR1 |
|
Row fetch w/o cursor