By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Convert Oracle PLSQL to SQL Server TSQL !

P: 1
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;
Nov 17 '08 #1
Share this Question
Share on Google+
1 Reply


debasisdas
Expert 5K+
P: 8,127
you can use any third party control or convert the code by yourself.
Nov 17 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.