Jan 9, 2015

While in sql server

declare @TotalRow int = (select COUNT(SalesRepCode) from i3s_SO_CRM_Report_Table)
declare @RunRow int = 1,@MaxRow int = 1
set @RunRow = (select min(ID) from #TBScheme)
set @MaxRow = (select MAX(ID) from #TBScheme)

while @RunRow<=@MaxRow
begin

declare @SchemeID nvarchar(50) = (select SchemeID from #TBScheme where ID = @RunRow)

if exists (select  COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='i3s_SO_CRM_Report_Table' and COLUMN_NAME=@SchemeID)
begin
    set @Sql= "UPDATE i3s_SO_CRM_Report_Table set "+@SchemeID+" = 0
    where "+@SchemeID+" is null"

    EXECUTE sp_executesql @Sql

    set @Sql=
    "if (select COUNT(SalesRepCode) from i3s_SO_CRM_Report_Table where "+@SchemeID+" = 0 OR "+@SchemeID+" is null) = "+cast(@TotalRow as varchar(50))+
                "ALTER TABLE i3s_SO_CRM_Report_Table DROP COLUMN "+@SchemeID

    EXECUTE sp_executesql @Sql
end

set @RunRow += 1

end

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib