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 Go to next message
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 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Order by issue
Next Topic: Lock the records While deleting
Goto Forum:
  


Current Time: Sat Dec 10 08:40:59 CST 2016

Total time taken to generate the page: 0.05325 seconds