Home » SQL & PL/SQL » SQL & PL/SQL » Change code from oracle to sqlserver 2008
Change code from oracle to sqlserver 2008 [message #425383] |
Thu, 08 October 2009 21:25  |
carillpower
Messages: 48 Registered: April 2009 Location: malaysia
|
Member |

|
|
Hi all,
i kinda new with SQLserver2008..currently i have a code that generates some function and its work on oracle SQL. What i should do now is how to convert the code to MSSQL server code. Kindly need all of guru's out there to guide me on this.
Here are the codes
Quote:
create or replace
PROCEDURE A (tbl_name varchar2 default ' ',
l_name varchar2 default ' ',
f_name varchar2 default ' ',
full_name varchar2 default ' ',
ssn varchar2 default ' ',
gender_col varchar2 default ' ',
employee_id varchar2 default ' ') IS
v_tbl varchar2(30) := tbl_name;
v_lname varchar2(30) := l_name;
v_fname varchar2(30) := f_name;
v_fullname varchar2(30) := full_name;
v_ssn varchar2(30) := ssn;
v_gender varchar2(30) := gender_col;
v_empid varchar2(30) := employee_id;
v_num number(10) := 1;
v_row integer;
proc_id varchar2(30) := 'Change process';
V_ERR_CD VARCHAR2(40);
V_ERR_NAME VARCHAR2(100);
BEGIN
if (v_tbl = ' ') then
dbms_output.put_line('Must put table name');
else
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v_tbl INTO v_row;
if (v_lname <> ' ') and (v_empid <> ' ') then
execute immediate '
update
(select a.'||v_lname||' a_lname, a.'||v_empid||' a_empid, b.last_name b_lname, b.employee_id b_emplid
from '||v_tbl||' a, CHANGED b
where b.emplid = a.'||v_empid||')
set a_lname= upper(b_lname) ';
end if;
--------------------------------------------------------------------------------------------------------------------------------
if (v_fname <> ' ') and (v_empid <> ' ') and (v_gender <> ' ')then
execute immediate '
update
(select a.'||v_fname||' a_fname, a.'||v_empid||' a_empid, a.'||v_gender||' a_gender,
b.male_first_name b_mfname, b.female_first_name b_ffname, b.unisex_name b_uname, b.employee_id b_emplid
from '||v_tbl||' a, CHANGED b
where b.emplid = a.'||v_empid||')
set a_fname= decode(a_gender,''U'', upper(b_uname),(decode(a_gender,''F'', upper(b_ffname), upper(b_mfname))))';
end if;
-----------------------------------------------------------------------------------------------------------------------------------
if (v_fullname <> ' ') and (v_empid <> ' ') and (v_gender <> ' ')then
execute immediate '
update
(select a.'||v_fullname||' a_full, a.'||v_empid||' a_empid, a.'||v_gender||' a_gender,
b.male_first_name b_mfname, b.female_first_name b_ffname, b.unisex_name b_uname, b.last_name b_lname, b.employee_id b_emplid
from '||v_tbl||' a, CHANGED b
where b.emplid = a.'||v_empid||')
set a_full=
concat( upper(b_lname) ||'' , '',
(decode(a_gender,''U'', upper(b_uname),(decode(a_gender,''F'', upper(b_ffname), upper(b_mfname))))))';
end if;
------------------------------------------------------------------------------------------------------------------------------------- --
if (v_lname <> ' ') and (v_empid <> ' ') then
dbms_output.put_line(v_row||' '||v_lname||' from table '||v_tbl||' have been change');
end if;
if (v_fname <> ' ') and (v_empid <> ' ') and (v_gender <> ' ') then
dbms_output.put_line(v_row||' '||v_fname||' from table '||v_tbl||' have been change');
end if;
if (v_fullname <> ' ') and (v_empid <> ' ') and (v_gender <> ' ') then
dbms_output.put_line(v_row||' '||v_fullname||' from table '||v_tbl||' have been change');
end if;
if (v_ssn <> ' ') then
execute immediate '
update '||v_tbl||' set
'||v_ssn||'= translate('||v_ssn||',''0123456789'',''5678901234'')
where rownum <= '||v_row||'';
dbms_output.put_line(v_row||' '||v_ssn||' from table '||v_tbl||' have been change');
end if;
end if;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error Encountered ' || substr(sqlerrm,1,200));
V_ERR_CD := SQLCODE;
V_ERR_NAME :=SQLERRM;
IF V_ERR_CD = 'ORA-01401' THEN
dbms_output.put_line('Procedure Compiled with Errors');
END IF;
END A;
Basically it loads Procedure A which will take some input from user...then it will change data from temporary table CHANGED to input table data
|
|
|
Re: Change code from oracle to sqlserver 2008 [message #425397 is a reply to message #425383] |
Fri, 09 October 2009 00:39  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
As this is an Oracle forum and most of us don't know SQL Server language I think you should have a better chance if you post this in a SQL Server forum.
It is always easier to translate from a foreign language to your native one than the opposite.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Thu Feb 13 16:38:09 CST 2025
|