SQL Procedure

This procedure is supposed to  deal with those number which  have decimal part and it's sum should be stable.

 

 

USE [CHSHMSSGMLICENSE]
GO

/****** Object: StoredProcedure [dbo].[Perfect_Rounding] Script Date: 07/16/2014 14:40:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Perfect_Rounding]
@Table [varchar](500),
@column [varchar](500)
WITH EXECUTE AS CALLER
AS
declare @sqlstr varchar(5000)
declare @Sum_Bf int
declare @Sum_Af int
declare @Difference int
declare @i int

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rounding_temp]') AND type in (N'U'))
DROP TABLE [dbo].[Rounding_temp]

set @sqlstr=
'
select
*,'+@column+' as Round_Col
into
dbo.Rounding_temp
from
'+@table

exec (@sqlstr)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rounding_Sum_Before]') AND type in (N'U'))
DROP TABLE [dbo].[Rounding_Sum_Before]

create table [dbo].[Rounding_Sum_Before]
(
Sum_Before float
)

set @sqlstr=
'
insert into [dbo].[Rounding_Sum_Before]
select sum('+@column+')
from '+@Table

exec (@sqlstr)

alter table [Rounding_temp] add Integer_Part int
alter table [Rounding_temp] add Decimal_Part float

update [Rounding_temp]
set Integer_Part=cast(Round_Col as int)

update [Rounding_temp]
set Decimal_Part=Round_Col-cast(Integer_Part as float)

select @sum_bf=round(sum_before,0)
from [Rounding_Sum_Before]

select @sum_af=sum(Integer_Part)
from [Rounding_temp]

select @difference=round((@sum_bf-@sum_af),0)

print(@sum_bf)
print(@sum_af)
print(@difference)

set @sqlstr=
'
update B2
set Integer_Part=Integer_Part+1
from
(
select top '+cast(@difference as varchar(10))+' * from [Rounding_temp] order by Decimal_Part desc
)as B2
'
exec (@sqlstr)

set @sqlstr=
'update [Rounding_temp]
set '+@column+'=Integer_Part'

exec (@sqlstr)

alter table [Rounding_temp] drop column Round_Col
alter table [Rounding_temp] drop column Integer_Part
alter table [Rounding_temp] drop column Decimal_Part

set @sqlstr=
'
delete from '+@table+'
'
exec (@sqlstr)

set @sqlstr=
'
insert into '+@table+'
select * from [Rounding_temp]
'
exec (@sqlstr)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rounding_temp]') AND type in (N'U'))
DROP TABLE [dbo].[Rounding_temp]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rounding_Sum_Before]') AND type in (N'U'))
DROP TABLE [dbo].[Rounding_Sum_Before]