在SQL Server中動态修改資料表結構

因為需要編寫一個統計字段和統計内容都不确定的報表，需要對報表結構進行動态驗證，根據業務需要調整報表的資料結構，我通過一下的存儲過程來實作的。以下代碼在SQL Server 2000 + SP4中調試通過。

if exists(select * from sysobjects where lower(name)=lower('up_AddCol4Obbr') and xtype='P')

 drop procedure up_AddCol4Obbr 

go

create procedure up_AddCol4Obbr

 @strTable nvarchar(100),

 @strColName nvarchar(100),

 @strType nvarchar(100)

as

begin

 declare @strSQL nvarchar(1000)

 if not exists(select * from syscolumns where lower(name)=lower(@strColName) and id in (select id from sysobjects where lower(name)=lower(@strTable) ))

 begin  

  select @strSQL = N'alter table ' + @strTable + ' add ' + @strColName + ' ' + @strType

  exec sp_executesql @strSQL

 end

 else

 begin

  select @strSQL = N'alter table ' + @strTable + ' alter column ' + @strColName + ' ' + @strType

end

if exists(select * from sysobjects where lower(name)=lower('up_CheckCols4Obbr') and xtype='P')

 drop procedure up_CheckCols4Obbr 

create procedure up_CheckCols4Obbr

 declare @nColCnts smallint, @nShopCnts smallint, @nCol smallint, @nShop smallint

 declare @strColName nvarchar(30), @strType nvarchar(50), @strTable nvarchar(50)

 select @nColCnts = count(*) from u_obbc

 select @nShopCnts = count(*) from u_obbs

 select @nCol=1, @nShop=1, @strTable='U_OBBR'

  select @strColName = 'TC' + right('00'+cast(@nCol as nvarchar(10)),2)

  select @strType = dType from joyouext.dbo.u_obbc where colId = @nCol

  exec up_AddCol4Obbr @strTable, @strColName, @strType

  select @nCol = @nCol + 1

  set @nCol=1

  begin

   select @strColName = 'BC' + right('00'+cast(@nShop as nvarchar(10)),2) + right('00'+cast(@nCol as nvarchar(10)),2)

   select @strType = dType from joyouext.dbo.u_obbc where colId = @nCol

   exec up_AddCol4Obbr @strTable, @strColName, @strType

   select @nCol = @nCol + 1

  end

  select @nShop = @nShop + 1

 end 

本文轉自foresun  51CTO部落格，原文連結：http://blog.51cto.com/foresun/44182，如需轉載請自行聯系原作者