Covert Oracle SQL to MSSQL statement?

[Philip Mette]

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.
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*/

tot_pay number ;
rec_upd varchar2(1);
todays_date date;
BEGIN
dbms_output.put_line('inside upd_activity ');
SELECT SYSDATE INTO todays_date FROM DUAL;
FOR ACT_CUR_REC1 IN ACT_CUR1
LOOP
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;
BEGIN
dbms_output.put_line('inside upd_activity1111 ');
FOR ACT_CUR_REC2 IN ACT_CUR2
LOOP
BEGIN
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;
dbms_output.put_line('tot_pay =');
IF tot_pay >= .9 * ACT_CUR_REC2.PROMISE_AMT_1 THEN
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
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 */
/

[Bent Stigsen]

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 */
/