
Create PROCEDURE [dbo].[ChangeGoodsCode]   @Old_code Varchar(20),@New_code varchar(20), @output tinyint output
AS
Declare @TableName Varchar(50)
Declare @columnName Varchar(20)
Declare @ExecCommand varchar(500)

begin try
begin tran
update kalaid set k_code=@New_code where K_Code=@Old_code
set @Old_code=''''+ @Old_code + ''''
set @New_code=''''+@New_code+''''
Declare ChangeCodeValue cursor For
SELECT SysOb.name as TableName, SysCol.name as TablecolumnName
FROM   sys.objects SysOb INNER JOIN sys.columns SysCol
ON SysOb.object_id = SysCol.object_id
where SysOb.type = 'U' and (SysCol.name='k_code' or syscol.name='GoodCode'
or SysCol.name='GoodsCode' or syscol.name='Kala_Code' or SysCol.name='StockCode'
or SysCol.name='GoodsCode')
order by SysOb.name

Open ChangeCodeValue
Fetch Next From ChangeCodeValue Into @TableName, @columnName
While @@Fetch_Status = 0 
Begin
  set @ExecCommand=' Update '+@TableName+'  Set ' 
  +@columnName +'='+@New_code+' where '+@columnName +'='+@Old_code
   Exec(@ExecCommand)
  Fetch Next From ChangeCodeValue Into @TableName, @columnName
End
Close ChangeCodeValue
Deallocate ChangeCodeValue
Set @output = 1
commit tran
 return @output
end try
begin catch
  IF CURSOR_STATUS('global','ChangeCodeValue') = 1
  BEGIN
    CLOSE ChangeCodeValue
	DEALLOCATE ChangeCodeValue
  END  

  DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorSeverity INT, @ErrorState INT;
  SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
  Set @output =0
  ROLLBACK TRANSACTION;
  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
  return @output
end catch





  
 
   






