ÿþif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[STORPROC_TRANSFEREMPLOYEE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[STORPROC_TRANSFEREMPLOYEE]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE STORPROC_TRANSFEREMPLOYEE
 @FROMDATABASE varchar(30), @TODATABASE varchar(30), @FROMSTAFFNO varchar(25), @TOSTAFFNO varchar(25),
 @DELETEEMP varchar(1), @TRANSFERED varchar(1) output
 AS
 Begin 
 declare @USEDATABASE nvarchar(50)
 declare @SQLSTRING nvarchar(4000)
 declare @FIELDCOUNT integer
 declare @SQLFIELD varchar(50)
 declare @SQLFIELDLIST nvarchar(4000)
 declare @RECCOUNT integer
 declare @SQLFROMKEY varchar(10) 
 declare @SQLTOKEY varchar(10) 
 BEGIN DISTRIBUTED TRANSACTION
 create table #TEMPFORPKEY ( PKEY integer )
 if @@Error <> 0 goto ErrorSection
 declare @SQLFIELD_MASTER varchar(50)
 declare @FIELDCOUNT_MASTER integer
 declare @SQLFIELDLIST_MASTER nvarchar(4000)
 set @FIELDCOUNT_MASTER = 0
 declare CURSOR_FOR_MASTER_FIELDS cursor for SELECT FIELDNAME from FIELDDEFS where TABLENAME = 'MASTER' and FIELDNAME not in ('STAFFNO','REPORTSTO', 'REPORTSTOSTAFFNO')
 OPEN CURSOR_FOR_MASTER_FIELDS
 FETCH NEXT FROM CURSOR_FOR_MASTER_FIELDS into @SQLFIELD_MASTER
 while (@@FETCH_STATUS <> -1)
 begin
 if @FIELDCOUNT_MASTER = 0 
 set @SQLFIELDLIST_MASTER = @SQLFIELD_MASTER
 else
 set @SQLFIELDLIST_MASTER = @SQLFIELDLIST_MASTER + ', '+ @SQLFIELD_MASTER
 set @FIELDCOUNT_MASTER = @FIELDCOUNT_MASTER + 1
 FETCH NEXT FROM CURSOR_FOR_MASTER_FIELDS into @SQLFIELD_MASTER
 end
 close CURSOR_FOR_MASTER_FIELDS
 deallocate CURSOR_FOR_MASTER_FIELDS
 Exec(' insert into '+ @TODATABASE +'.dbo.MASTER (' + @SQLFIELDLIST_MASTER + ',STAFFNO) Select '+
 @SQLFIELDLIST_MASTER + ','''+ @TOSTAFFNO +''' from ' + @FROMDATABASE + '.dbo.MASTER where (STAFFNO = "'+ @FROMSTAFFNO + '")')
	if @@Error <> 0 goto ErrorSection
 declare @TABLENAME varchar(25)
 declare @APP_TRANSFERED varchar(1)
 declare @PREVTABLENAME varchar(25) 
 set @SQLSTRING = ''
 create table #FIELDNAMES ( FIELDNAME varchar(50) )
 declare CURSOR_FOR_TABLELIST cursor for
 SELECT DISTINCT A.TABLENAME from TABLEDEFS A, FIELDDEFS B where ((A.TYPE = 'E') or 
	(A.TABLENAME = 'ATTACHEDDOCUMENTS')) and  (A.TABLENAME not in 
	('MASTER', 'APPRAISAL_ASSESSMENT', 'APPRAISAL_OBJECTIVE', 'APPRAISAL_TRAININGPLANNED', 'CURRENTPAY')) 
 and (B.FIELDNAME = 'PKEY') and (B.TABLENAME = A.TABLENAME)
 OPEN CURSOR_FOR_TABLELIST
	FETCH NEXT FROM CURSOR_FOR_TABLELIST into @TABLENAME
 while (@@FETCH_STATUS <> -1)
 begin
	set @FIELDCOUNT = 0 
	set @SQLFIELD = ''
	set @SQLFIELDLIST = ''
	set @SQLSTRING = ''
	delete from #FIELDNAMES
	set @SQLSTRING = ' insert into #FIELDNAMES select FIELDNAME from FIELDDEFS where (TABLENAME = "'
	+ @TABLENAME +'") and (FIELDNAME <> ''' + 'PKEY' +''') and (FIELDNAME <> ''' + 'STAFFNO' +''')' 
	EXECUTE sp_executesql @SQLSTRING
	if @@Error <> 0 goto ErrorPara
	declare CURSOR_FOR_FIELDS cursor for SELECT FIELDNAME from #FIELDNAMES 
	OPEN CURSOR_FOR_FIELDS
	FETCH NEXT FROM CURSOR_FOR_FIELDS into @SQLFIELD
	while (@@FETCH_STATUS <> -1)
 begin
	if @FIELDCOUNT = 0
	set @SQLFIELDLIST = @SQLFIELD
	else
	set @SQLFIELDLIST = @SQLFIELDLIST + ', '+ @SQLFIELD
	set @FIELDCOUNT = @FIELDCOUNT + 1 
	FETCH NEXT FROM CURSOR_FOR_FIELDS into @SQLFIELD
 End
 close CURSOR_FOR_FIELDS
 deallocate CURSOR_FOR_FIELDS
 declare @IFROMPKEY int
	declare @ITOPKEY int
	declare @SSTRING nvarchar(200)
	delete from #TEMPFORPKEY
 set @SSTRING = 'insert into #TEMPFORPKEY select PKEY from ' + @FROMDATABASE + '.dbo.' + @TABLENAME +
	' where STAFFNO = ''' + @FROMSTAFFNO +'''' 
 execute sp_executesql @SSTRING
	if @@Error <> 0 goto ErrorPara
	declare CURSOR_FOR_SUBRECORDS cursor for select PKEY from #TEMPFORPKEY
	OPEN CURSOR_FOR_SUBRECORDS
	FETCH NEXT FROM CURSOR_FOR_SUBRECORDS into @IFROMPKEY
 while (@@FETCH_STATUS <> -1)
	begin
	set @SSTRING = @TODATABASE + '.dbo.GETTABLEPKEY_P ' 
	execute @SSTRING @TABLENAME, @ITOPKEY output 
	Set @SQLFROMKEY  = convert(varchar(10), @IFROMPKEY)
	Set @SQLTOKEY  = convert(varchar(10), @ITOPKEY) 
Exec(' insert into ' + @TODATABASE + '.dbo.' + @TABLENAME + ' ( '+ @SQLFIELDLIST + ',STAFFNO, PKEY )' +
 ' Select ' + @SQLFIELDLIST + ' , ''' + @TOSTAFFNO +  ''' , ' + @SQLTOKEY + ' from ' + @FROMDATABASE +
	'.dbo.' + @TABLENAME + ' where STAFFNO = ''' + @FROMSTAFFNO + ''' and PKEY = ' + @SQLFROMKEY )
	if @@Error <> 0 goto ErrorPara_CURSOR_FOR_SUBRECORDS
	select @APP_TRANSFERED = 'T'
	declare @FROMPKEY varchar(30)
	if @TABLENAME = 'APPRAISAL' begin
	execute STORPROC_APPRAISAL_TRANSFER @FROMDATABASE, @TODATABASE, @FROMSTAFFNO, @TOSTAFFNO,
	@ITOPKEY, @IFROMPKEY, @APP_TRANSFERED output
	if @@Error <> 0 goto ErrorPara_CURSOR_FOR_SUBRECORDS 
	if @APP_TRANSFERED <> 'T' goto ErrorPara_CURSOR_FOR_SUBRECORDS
	end
	if @@Error <> 0 goto ErrorPara_CURSOR_FOR_SUBRECORDS
	FETCH NEXT FROM CURSOR_FOR_SUBRECORDS into @IFROMPKEY
 end
 close CURSOR_FOR_SUBRECORDS
	deallocate CURSOR_FOR_SUBRECORDS
 delete from #TEMPFORPKEY
 if @@Error <> 0 goto ErrorPara
	FETCH NEXT FROM CURSOR_FOR_TABLELIST into @TABLENAME
 end
 close CURSOR_FOR_TABLELIST
 deallocate CURSOR_FOR_TABLELIST
 delete from #FIELDNAMES
 drop table #FIELDNAMES
 delete from #TEMPFORPKEY
 drop table #TEMPFORPKEY
 declare CURSOR_FOR_NONPKEY_TABLELIST cursor for
	select TABLENAME,FIELDNAME FROM FIELDDEFS where TABLENAME in
	('DAILYATTENDANCE',  'PERSONALALLOWANCES', 'PHOTOS', 'WEEKLYATTENDANCE') AND (FIELDNAME <> 'STAFFNO')
 set @SQLFIELDLIST = ''
 set @TABLENAME = ''
 OPEN CURSOR_FOR_NONPKEY_TABLELIST
 FETCH NEXT FROM CURSOR_FOR_NONPKEY_TABLELIST into @TABLENAME, @SQLFIELD
 set @PREVTABLENAME = @TABLENAME
 while (@@FETCH_STATUS <> -1)
 begin
 if @PREVTABLENAME = @TABLENAME
 if len(@SQLFIELDLIST) > 0
	set @SQLFIELDLIST = @SQLFIELDLIST +', '+ @SQLFIELD 
	else
	set @SQLFIELDLIST = @SQLFIELD
	else begin
	set @SQLSTRING = ' declare @RECCOUNT int SET @RECCOUNT = ' 
 set @SQLSTRING = @SQLSTRING + '( SELECT COUNT(*) from ' + @FROMDATABASE + '.dbo.' + @PREVTABLENAME + ' where STAFFNO = "'+  @FROMSTAFFNO+ '")'
 set @SQLSTRING = @SQLSTRING +' if @RECCOUNT > 0 '
 set @SQLSTRING = @SQLSTRING + ' insert into  ' + @TODATABASE + '.dbo.' + @PREVTABLENAME + ' ( ' +@SQLFIELDLIST +',STAFFNO) '
 set @SQLSTRING = @SQLSTRING + ' Select '+@SQLFIELDLIST + ',''' + @TOSTAFFNO +''' from ' + @FROMDATABASE + '.dbo.' + @PREVTABLENAME 
 set @SQLSTRING = @SQLSTRING +  '  WHERE  (STAFFNO = "' + @FROMSTAFFNO + '")'
 EXECUTE sp_executesql @SQLSTRING 
 if @@Error <> 0 goto ErrorPara_CURSOR_FOR_NONPKEY_TABLELIST
 set @SQLFIELDLIST = @SQLFIELD 
 end
 set @PREVTABLENAME = @TABLENAME
 FETCH NEXT FROM CURSOR_FOR_NONPKEY_TABLELIST into @TABLENAME, @SQLFIELD 
 end
 set @SQLSTRING = ' declare @RECCOUNT int SET @RECCOUNT = ' 
 set @SQLSTRING = @SQLSTRING + '( SELECT COUNT(*) from ' + @FROMDATABASE + '.dbo.' + @PREVTABLENAME + ' where STAFFNO = "'+  @FROMSTAFFNO+ '")' 
 set @SQLSTRING = @SQLSTRING + ' if @RECCOUNT > 0 ' 
 set @SQLSTRING = @SQLSTRING +  ' insert into  ' + @TODATABASE + '.dbo.' + @PREVTABLENAME  + ' ( ' +@SQLFIELDLIST+',STAFFNO) '
 set @SQLSTRING = @SQLSTRING +  '  Select '+@SQLFIELDLIST + ',''' + @TOSTAFFNO +''' from ' + @FROMDATABASE + '.dbo.' + @PREVTABLENAME 
 set @SQLSTRING = @SQLSTRING +  '  WHERE  (STAFFNO = "' + @FROMSTAFFNO + '")' 
 EXECUTE sp_executesql @SQLSTRING 
 if @@Error <> 0 goto ErrorPara_CURSOR_FOR_NONPKEY_TABLELIST 
 Close CURSOR_FOR_NONPKEY_TABLELIST 
 deallocate CURSOR_FOR_NONPKEY_TABLELIST
 if @DELETEEMP = '1'
 begin
 set @SQLSTRING = 'DELETE FROM ' + @FROMDATABASE + '.dbo.MASTER ' 
 set @SQLSTRING = @SQLSTRING + ' where (STAFFNO = "' + @FROMSTAFFNO + '")' 
 EXECUTE sp_executesql  @SQLSTRING
 end
 if @@Error <> 0 goto ErrorSection 
 if @DELETEEMP = '1'
 begin
 set @SQLSTRING ='DELETE FROM ' + @FROMDATABASE + '.dbo.CURRENTPAY '
 set @SQLSTRING = @SQLSTRING + ' where (STAFFNO = "' + @FROMSTAFFNO + '")' 
 EXECUTE sp_executesql  @SQLSTRING 
 end
 if @@Error <> 0 goto ErrorSection 
 COMMIT TRANSACTION
 select @TRANSFERED = 'T'
 return(0)
 ErrorPara_CURSOR_FOR_NONPKEY_TABLELIST: 
 begin 
 close CURSOR_FOR_NONPKEY_TABLELIST
 deallocate CURSOR_FOR_NONPKEY_TABLELIST
 goto ErrorSection
 end
 ErrorPara_CURSOR_FOR_SUBRECORDS: 
 begin
 close CURSOR_FOR_SUBRECORDS
 deallocate CURSOR_FOR_SUBRECORDS
 goto ErrorPara
 end
 ErrorPara:
 begin
 close CURSOR_FOR_TABLELIST
 deallocate CURSOR_FOR_TABLELIST
 goto ErrorSection
 end
 ErrorSection:
 begin
 ROLLBACK TRANSACTION
 select @TRANSFERED = 'F'
 return(-1)
end
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO