因为需要编写一个统计字段和统计内容都不确定的报表,需要对报表结构进行动态验证,根据业务需要调整报表的数据结构,我通过一下的存储过程来实现的。以下代码在SQL Server 2000 + SP4中调试通过。
if exists(select * from sysobjects where lower(name)=lower('up_AddCol4Obbr') and xtype='P')
drop procedure up_AddCol4Obbr
go
drop procedure up_AddCol4Obbr
go
create procedure up_AddCol4Obbr
@strTable nvarchar(100),
@strColName nvarchar(100),
@strType nvarchar(100)
as
begin
declare @strSQL nvarchar(1000)
@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
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
else
begin
select @strSQL = N'alter table ' + @strTable + ' alter column ' + @strColName + ' ' + @strType
exec sp_executesql @strSQL
end
end
go
end
end
go
if exists(select * from sysobjects where lower(name)=lower('up_CheckCols4Obbr') and xtype='P')
drop procedure up_CheckCols4Obbr
go
drop procedure up_CheckCols4Obbr
go
create procedure up_CheckCols4Obbr
as
begin
declare @nColCnts smallint, @nShopCnts smallint, @nCol smallint, @nShop smallint
declare @strColName nvarchar(30), @strType nvarchar(50), @strTable nvarchar(50)
as
begin
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 @nShopCnts = count(*) from u_obbs
select @nCol=1, @nShop=1, @strTable='U_OBBR'
while @nCol<
=@nColCnts
begin
select @strColName = 'TC' + right('00'+cast(@nCol as nvarchar(10)),2)
select @strType = dType from joyouext.dbo.u_obbc where colId = @nCol
begin
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
end
end
while @nShop<
=@nShopCnts
begin
set @nCol=1
while @nCol< =@nColCnts
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
begin
set @nCol=1
while @nCol< =@nColCnts
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
end
go
end
end
go
转载于:https://blog.51cto.com/foresun/44182