|
Hello all,
I am new to convert PLSQL to TSQL. Any expertise in SQL, please help me to do convert the belowed PLSQL procedure to SP in SQL server. I really appreciate your time. Thanks in advance.
==============================================
PROCEDURE SaveTestAs(P_ProgramID NUMBER,
P_ProgramName VARCHAR2)
IS
L_TestId INTEGER := 0;
CURSOR L_Program IS
SELECT *
FROM PROGRAMS
WHERE PROGRAM_ID = P_ProgramID;
CURSOR L_ProgramTest IS
SELECT *
FROM PROGRAM_TEST
WHERE PROGRAM_ID = P_ProgramID;
CURSOR L_ProgramNums(C_TestID IN NUMBER) IS
SELECT *
FROM PROGRAM_NUMERICS
WHERE TEST_ID = C_TestId;
CURSOR L_ProgramStr(C_TestID IN NUMBER) IS
SELECT *
FROM PROGRAM_STRINGS
WHERE TEST_ID = C_TestID;
BEGIN
FOR R_Programs IN L_Program LOOP
INSERT INTO PROGRAMS
VALUES(PROGRAMID.NEXTVAL,
P_ProgramName,
R_Programs.Program_Description,
SYSDATE,
R_Programs.Created_By,
'',
R_Programs.Station_NO);
END LOOP;
FOR R_PT IN L_ProgramTest LOOP
INSERT INTO PROGRAM_TEST
VALUES(TESTID.NEXTVAL,
PROGRAMID.CURRVAL,
R_PT.TEST_NUMBER,
R_PT.TESTDEF_ID,
R_PT.FAIL_MESSAGE,
R_PT.FAIL_HELPFILE,
R_PT.ACTIVE,
R_PT.CONTINUE_ON_FAIL);
FOR R_PN IN L_ProgramNums(R_PT.TEST_ID) LOOP
INSERT INTO PROGRAM_NUMERICS
VALUES(NUMERIC_ID.NEXTVAL,
TESTID.CURRVAL,
R_PN.NUM_PARAM_NUM,
R_PN.NUMERIC_PARAMETER,
R_PN.HELP_DESCRIPTION);
END LOOP;
FOR R_PS IN L_ProgramStr(R_PT.TEST_ID) LOOP
INSERT INTO PROGRAM_STRINGS
VALUES(STRING_ID.NEXTVAL,
TESTID.CURRVAL,
R_PS.STRING_PARAM_NUM,
R_PS.STRING_PARAMETER,
R_PS.HELP_DESCRIPTION);
END LOOP;
END LOOP;
END SaveTestAs;
| 
