Improving a stored procedure

Hi All

I have the following Stored procedure....



CREATE PROCEDURE [sp_update_staff_table]
as
declare
 @EMPLID char (6),
 @EMPID int,
 @STAFF_NUMBER int,
 @COMPANY char (3),
 @SURNAME char (30),
 @PREF_NAME char (50),
 @Known_as_SP char (50),
 @Location_SP char (10),
 @Account_Name_SP char (50),
 @EMP_STATUS_SP char (1),
 @Extension_SP int,
 @unit_code_SP smallint,
 @NAME char (50),
 @UNITNO char (6),
 @DIVNO char (6),
 @EMP_STATUS char (30),
 @LOCATION char (10)

create table #pims_interface (EMPLID char (6), EMPID int, COMPANY char (3), SURNAME char (30), PREF_NAME char (50), NAME char (50), UNITNO char (6), DIVNO char (6), EMP_STATUS char (30), LOCATION char (10))
--udpated to use GCD PIMS_Interface and to use the Host record
INSERT INTO #pims_interface select ltrim(rtrim(emplid)) as emplid, convert(int,emplid) as empid, ltrim(rtrim(company)) as company, ltrim(rtrim(Last_name)) as Last_name, ltrim(rtrim(preferred_name)) as Preferred_name, ltrim(rtrim(name)) as [name], ltrim(rtrim(log_unit_cd)) as log_unit_cd, ltrim(rtrim(log_division_cd)) as log_division_cd, ltrim(rtrim(employee_status)) as employee_status, ltrim(rtrim(location)) as location from GCDDEV.GCD.dbo.Pims_interface as a where a.EMPL_RCD# = (select max(EMPL_RCD#) from GCDDEV.GCD.dbo.Pims_interface where emplid = a.emplid) order by emplid
--INSERT INTO #pims_interface select ltrim(rtrim(emplid)) as emplid, convert(int,emplid) as empid, ltrim(rtrim(company)) as company, ltrim(rtrim(Last_name)) as Last_name, ltrim(rtrim(preferred_name)) as Preferred_name, ltrim(rtrim(name)) as [name], ltrim(rtrim(log_unit_cd)) as log_unit_cd, ltrim(rtrim(log_division_cd)) as log_division_cd, ltrim(rtrim(employee_status)) as employee_status, ltrim(rtrim(location)) as location from GCD.GCD.dbo.Pims_interface order by emplid
--INSERT INTO #pims_interface select ltrim(rtrim(emplid)) as emplid, convert(int,emplid) as empid, ltrim(rtrim(company)) as company, ltrim(rtrim(surname)) as Last_name, ltrim(rtrim(preferrednamefirst)) as Preferred_name, ltrim(rtrim(firstname)) as [name], ltrim(rtrim(logunitcd)) as log_unit_cd, ltrim(rtrim(logdivisioncd)) as log_division_cd, ltrim(rtrim(emplstatus)) as employee_status, ltrim(rtrim(location)) as location from GCD.GCD.dbo.Pims_interface order by emplid
create table #Staff_Numbers_NT_Accounts (staff_number int, associated_NT_account varchar(40),  surname varchar (50), forename varchar(50))
insert into #Staff_Numbers_NT_Accounts select staff_number, associated_NT_account, surname, forename from GPS5A.isd.dbo.Staff_Numbers_NT_Accounts
create table #Personal_Details (Phone_Extension int, Intl_staff_number int, Current_Location varchar(10))
insert into #Personal_Details select Phone_Extension, intl_staff_number, ltrim(rtrim(Current_Location)) from GPS5A.warehouse.dbo.Personal_Details
--set alll the units in the staff table to 0
update staff set unit_code = '0'
declare data scroll cursor
   for select * from #pims_interface order by emplid
open data
fetch next from data
into
 @EMPLID,
 @EMPID,
 @COMPANY,
 @SURNAME,
 @PREF_NAME,
 @NAME,
 @UNITNO,
 @DIVNO,
 @EMP_STATUS,
 @LOCATION
while (@@fetch_status = 0)
begin
--if the employee does not exist in staff then insert the id
if @empid not in (select staff_number from staff)
 begin
  insert staff values(' ',' ',' ',' ',@empid,NULL,0,' ',0,' ','own','N',NULL)
   --make sure that there is only one record for the details supplied.
  if (select count(staff_number) from #Staff_Numbers_NT_Accounts where staff_number = @empid and surname = @SURNAME and forename = @NAME) = 1
   begin
    update staff set account_name = (select substring(associated_NT_account,(charindex('\',associated_NT_account)+1),100) from #Staff_Numbers_NT_Accounts where staff_number = @empid and surname = @SURNAME and forename = @NAME) where  staff_number = @empid
   end
 end
 
--update the staff table
--If there is no value in the Pref_name then assign Name as Pref Name
if ltrim(rtrim(@PREF_NAME)) = '' or @PREF_NAME is null
 begin
  set @known_as_SP = @NAME
 end
else
 begin
   set @known_as_SP = @PREF_NAME
 end

--This is to set the Unit No to '0' if they are not in the lookup table.  From the above code employeees in the staff table may have a null of ' ' values as their Units
set @unit_code_SP = (select agr_unit from tbl_unitdiv where PIMS_UNIT = @UNITNO and PIMS_COMP = @COMPANY and PIMS_DIV = @DIVNO)

if ltrim(rtrim(@unit_code_SP))  = '' or ltrim(rtrim(@unit_code_SP)) is null
 Begin
      set @unit_code_SP = 0
 End

-- This to populate the extionsion variable
set @Extension_SP = (select Phone_Extension from #Personal_Details where intl_staff_number = @EMPID)

if ltrim(rtrim(@Extension_SP))  = '' or ltrim(rtrim(@Extension_SP)) is null
 Begin
      set @Extension_SP = 0
 End


--This is to set the location of employees.
set @Location_SP = (select Current_Location from #Personal_Details where intl_staff_number = @EMPID)
--If the employee's location is not in the personal details in warehouse then we use the Location value in PIMS
if ltrim(rtrim(@Location_SP))  = '' or ltrim(rtrim(@Location_SP)) is null
 Begin
      set @Location_SP = ltrim(rtrim(@Location))
 End

-- This is to Poluate the Status of the Employee
 if @EMP_STATUS = 'Active'
   begin
   set @EMP_STATUS_SP = 'Y'
   end
 if @EMP_STATUS != 'Active'
  begin
   set @EMP_STATUS_SP = 'N'
  end


-- This is to Populate the Account Name
 if (select count(staff_number) from #Staff_Numbers_NT_Accounts where staff_number = @empid and surname = @SURNAME and forename = @NAME) = 1
   begin
    set @account_name_SP = (select substring(associated_NT_account,(charindex('\',associated_NT_account)+1),100) from #Staff_Numbers_NT_Accounts where staff_number = @empid and surname = @SURNAME and forename = @NAME)
    end
Else
 Begin
       set @account_name_SP = ''
 End

--Finally the Update statement.
update staff set surname = ltrim(rtrim(@SURNAME)), forename_1 = ltrim(rtrim(@NAME)), known_as = ltrim(rtrim(@known_as_SP)), unit_code = @unit_code_SP, extension = @Extension_SP, location = ltrim(rtrim(@Location_SP)), active = ltrim(rtrim(@EMP_STATUS_SP)), account_Name = ltrim(rtrim(@account_name_SP)) where staff_number = @EMPID



   fetch next
    from data
into
 @EMPLID,
 @EMPID,
 @COMPANY,
 @SURNAME,
 @PREF_NAME,
 @NAME,
 @UNITNO,
 @DIVNO,
 @EMP_STATUS,
 @LOCATION
end
close data
deallocate data
drop table #pims_interface
drop table #Staff_Numbers_NT_Accounts
drop table #Personal_Details
GO


table GCDDEV.GCD.dbo.Pims_interface  has around 21000 record in it. Currently the PS takes 2 hrs to run!!!
any ideas?