SP for changing a Primary key value having references

-- Returns:0 on success, 1 if existing employee is not found, 2 if new number already exists, or > 2 if an error is encountered

/*This proc replaces an employee number*in a Master Table (Empdata), and all
*foreign tables. The employee number is*stored in a field named Emp_nbr.
*/
-- Initial Idea by T Runstein
-- Generalised By Bineesh Thomas
Create proc sp_ChangeFKValue
@OldID int, @NewID int,@PkTable Varchar(200),@PKField Varchar(200)
as
declare @TabKey int
declare @TabName sysname
declare @ColName sysname
declare @ErrTrap int
declare @lname varchar(50)
declare @fname varchar(50)
declare @mname varchar(30)
declare @dept int
declare @runStatement varchar(250)
Declare @qry as varchar(1000)
create table #TempTable
(TableKey int identity primary key not null,TableName sysname,ColName sysname null)
SET NOCOUNT ON

insert #TempTable (TableName, ColName)
(select SO.name as TableName, SS.name as ColName from sysobjects SO,
(select SC.* from syscolumns SC,
(select fkeyid, fkey from sysforeignkeys
where rkeyid =
(select id from sysobjects where name = @PkTable)) FF
where SC.id = FF.fkeyid and sc.colid = ff.fkey) SS
where SO.id = SS.id)

Set @qry='select count(*) cnt into ##cnt1 from ' + @PkTable + ' where ' + @PKField + ' = ' + ltrim(str(@OldID))
--Print @qry
exec(@qry)

set @ErrTrap =(Select top 1 cnt from ##cnt1 )
if (@ErrTrap <> 1)
return 1

Set @qry='select count(*) cnt into ##cnt2 from ' + @PkTable + ' where ' + @PKField + ' = ' + ltrim(str(@NewID))
exec(@qry)
set @ErrTrap = ( Select top 1 cnt from ##cnt2 )
if (@ErrTrap <> 0)
return 2
--Copy the information from the existing record to a new record . Check for errors before proceeding
BEGIN TRANSACTION
Set @qry='Select * into ##tmp1 from ' + @PkTable + ' Where ' + @PKField + '= ' + ltrim(str(@OldID))
exec (@qry)
Set @qry='Update ##tmp1 Set ' + @PKField + ' = ' + ltrim(str(@NewID ))
exec (@qry)
Set @qry ='Insert Into ' + @PkTable + ' Select * from ##tmp1'
exec (@qry)
set @ErrTrap = @@error
if (@ErrTrap <> 0)
begin
ROLLBACK TRANSACTION
return @ErrTrap
end
set @TabKey = 1
while not @TabKey is NULL
begin
set @TabName = (select TableName from #TempTable where TableKey = @TabKey)
set @ColName = (select ColName from #TempTable where TableKey = @TabKey)
set @runStatement = 'update [' + @TabName + '] set [' + @ColName + '] = ' + cast(@NewID as varchar(10)) + ' where [' + @ColName + '] = ' + cast(@OldID as varChar(10))
exec (@runStatement)
set @TabKey = (select min(TableKey) from #TempTable where TableKey > @TabKey)
end
Set @qry='delete '+ @PkTable +' where ' + @PKField + ' = ' + ltrim(str(@OldID))
exec(@qry)
if @@error <> 0
ROLLBACK TRANSACTION
else
COMMIT TRANSACTION
drop table #TempTable
drop table ##cnt1
drop table ##cnt2
drop table ##tmp1
return 0
set nocount off
GO