change strore procedure from Sybase to sql server

change strore procedure from Sybase to sql server

How can i change this Store Procedure for Sql Server. I try but i have not
success can you please help me . I am not understanding what is going in
this SP and what i will write in SQL SERVER 2008 .
if exists (select * from sys.systable where table_name = 'ATSROUTES')
begin
drop table ATSROUTES;
end
create table comet.ATSROUTES(
zip varchar(255) null,
route varchar(255) null,
drivernum varchar(255) null,
altserviceid varchar(255) null,
localorldrvnum varchar(255) null,
pickupzone varchar (255) null,
distcenter varchar (255) null,
altdispid varchar (255) null,
id integer null
);
load into table dbo.ATSROUTES from 'C:\Routes\ATS_Routes.csv' format ascii;
//load into table comet.ATSROUTES from 'C:\ATS_Routes.csv' format ascii;
if exists(select * from sys.sysprocedure where proc_name = 'updateroute')
begin
drop procedure updateroute;
end
CREATE PROCEDURE comet.updateroute ( /* parameter, ... */ )
/* RESULT ( column_name, ... ) */
BEGIN
declare @route varchar(255);
declare @zip varchar(255);
declare @routeid varchar(255);
declare @distcenter varchar(255);
declare @altdispid varchar(255);
declare ERR_NOTFOUND exception for sqlstate value '02000';
DECLARE priceinfo dynamic scroll CURSOR FOR SELECT zip, route from atsroutes;
OPEN priceinfo with hold;
lp: LOOP
FETCH next priceinfo INTO @zip,@route;
if(sqlstate = ERR_NOTFOUND) then begin
leave lp
end
if (@route is not null and @route <> '')
begin
if not exists (select * from routenames where routename = @ROUTE) begin
call COMET.GETNEWRECID2('ROUTENAMES','ROUTEID','ROUTENAMES',@ROUTEID);
insert into routenames (routeid,routename) values (@routeid,@ROUTE);
end
end
end loop;
close priceinfo;
END;
call updateroute;
drop procedure comet.updateroute;
update atsroutes set id = (select routeid from routenames where routename
= route);
update atsroutes set drivernum = trim(drivernum);
update atsroutes set altserviceid = trim(altserviceid);
update atsroutes set pickupzone = trim(pickupzone);
update atsroutes set distcenter = trim(distcenter);
update atsroutes set altdispid = trim(altdispid);
update atsroutes set drivernum = (case when (length(drivernum) = 3) then
'0'+drivernum else drivernum end),
//ikeadrivernum = (case when (length(ikeadrivernum) = 3) then
'0'+ikeadrivernum else ikeadrivernum end),
localorldrvnum = (case when (length(localorldrvnum) = 3) then
'0'+localorldrvnum else localorldrvnum end)
Thanks for your comments