From: ngcdo@thevoid.dk   
      
   Philip Mette wrote:   
   > I am in a crunch and need to covert this Oracle statement to MSSQL. Is   
   > there any Oracle/MSSQL experts out there that can help me? I do not   
   > understand the syntax enough to modify this.   
      
   I assume you have some experience with MSSQL's stored procedures.   
      
      
   > Thanks so much for any assistance. Here is the procedure.   
   >   
   >   
   > CREATE PROCEDURE UPD_ACTIVITY IS   
   >    CURSOR ACT_cur1   
   >    IS   
   >       SELECT  DISTINCT A.ACCT_NUM, A.DUE_DATE   
   >       FROM TBLCACSDAILYACTIVITIES A ; /* this picks the unique   
   > combination of acct and date*/   
      
   declare ACT_cur1 cursor local for   
           SELECT  DISTINCT A.ACCT_NUM, A.DUE_DATE   
           FROM TBLCACSDAILYACTIVITIES A   
      
   > 	  tot_pay number ;   
   > 	  rec_upd varchar2(1);   
   > 	  todays_date date;   
      
   declare @tot_pay float   
   declare @rec_upd varchar(1)   
   declare @todays_date datetime   
      
      
   > BEGIN   
   >    dbms_output.put_line('inside upd_activity ');   
      
   print 'inside upd_activity '   
      
      
   >    SELECT SYSDATE INTO todays_date FROM DUAL;   
      
   set @todays_date = (select getdate())   
      
      
   >    FOR ACT_CUR_REC1 IN ACT_CUR1   
   >    LOOP   
      
   No exact equivalence in MSSQL, you would do something like   
      
   open ACT_cur1   
   while (1=1)   
   begin   
      fetch next   
        from ACT_cur1   
        into @C1_ACCT_NUM, @C1_DUE_DATE  /*declare first*/   
      if (@@fetch_status <> 0) break   
      
   >        rec_upd := 'N';   
      
   set @rec_upd = 'N'   
      
   >    	   DECLARE   
   >    	   		  CURSOR ACT_CUR2   IS   
   > 			         SELECT  B.ACCT_NUM,   
   > B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,   
   > 	   		  		 		 B.TOTAL_DOLLARS_COLL   
   > 					 FROM TBLCACSDAILYACTIVITIES B   
   > 					 WHERE	 B.ACCT_NUM = ACT_CUR_REC1.ACCT_NUM   
   > 					 AND	 B.DUE_DATE = ACT_CUR_REC1.DUE_DATE   
   > 					 ORDER BY B.ACTIVITY_DATE   
   >        				 FOR UPDATE OF B.PROMISE_STATUS,B.TOTAL_DOLLARS_COLL   
   > NOWAIT;   
      
   Dont know the equivalence for NOWAIT, try without.   
   besides that, declare as the previous cursor   
      
      
   >    	   BEGIN   
   > 		  dbms_output.put_line('inside upd_activity1111 ');   
   > 		  FOR ACT_CUR_REC2 IN ACT_CUR2   
   > 		  LOOP   
   > 		  BEGIN   
      
   same as before   
      
   > 		  	   SELECT SUM(C.PAY_AMT) into tot_pay   
   > 			   FROM TBLCACSDAILYPAYMENTS C   
   > 			   WHERE  C.ACCT_NUM = ACT_CUR_REC2.ACCT_NUM   
   > 			   AND	  C.ACTIVITY_DATE >= ACT_CUR_REC2.ACTIVITY_DATE   
   > 			   AND	  C.ACTIVITY_DATE < ACT_CUR_REC2.ACTIVITY_DATE+10;   
      
   set @tot_pay = (   
            SELECT SUM(C.PAY_AMT)   
            FROM TBLCACSDAILYPAYMENTS C   
            WHERE  C.ACCT_NUM = @C2_ACCT_NUM         /*NB var*/   
            AND C.ACTIVITY_DATE >= @C2_ACTIVITY_DATE   
            AND C.ACTIVITY_DATE < @C2_ACTIVITY_DATE+10   
        )   
      
   > 	   		  dbms_output.put_line('tot_pay =');   
   > 		  	  IF tot_pay >= .9 *  ACT_CUR_REC2.PROMISE_AMT_1  THEN   
      
   if (tot_pay >= .9 *  @PROMISE_AMT_1)   
   begin   
      
   > 			  	 IF rec_upd = 'N' THEN  /* recs have not bee updated for promise   
   > status*/   
   > 			   	 	UPDATE TBLCACSDAILYACTIVITIES   
   > 				  	SET    PROMISE_STATUS = 'PK',   
   > 				  		   TOTAL_DOLLARS_COLL = tot_pay   
   > 				  	WHERE CURRENT OF ACT_CUR2;   
   > 				  	rec_upd := 'Y';   
   > 				 ELSIF rec_upd = 'Y' THEN   
      
   end   
   else if (@rec_upd = 'Y')   
   begin   
      
      
   > 			  	    UPDATE TBLCACSDAILYACTIVITIES   
   > 				  	SET    PROMISE_STATUS = 'IP'   
   > 				  	WHERE CURRENT OF ACT_CUR2;   
   > 			  	 END IF;   
   > 		  	  ELSIF tot_pay < .9 *  ACT_CUR_REC2.PROMISE_AMT_1  THEN   
   > 			  	   IF (ACT_CUR_REC2.ACTIVITY_DATE+10) > todays_date THEN   
   > 				   	  	UPDATE TBLCACSDAILYACTIVITIES   
   > 				  		SET    PROMISE_STATUS = 'OP'   
   > 				  		WHERE CURRENT OF ACT_CUR2;   
   > 						BREAK;   
   > 				   ELSE   
   > 				   		UPDATE TBLCACSDAILYACTIVITIES   
   > 				  		SET    PROMISE_STATUS = 'PB'   
   > 				  		WHERE CURRENT OF ACT_CUR2;   
   > 				   END IF;   
   > 			  END IF;   
   > 		   END;   
   > 		   END LOOP;  /* end of ACT_CUR_REC2 loop */   
   > 	   END;   
   >    END LOOP;   /*end of ACT_CUR_REC1 */   
   > END ; /*  end of proc */   
   > /   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)