declare @wagechinesename varchar(200),@wageenglishname varchar(200),@default int
declare @sql_string varchar(8000)
declare cur_for cursor for
select wagechinesename,wageenglishname,[default] from RL_wageItem
open cur_for
fetch next from cur_for into @wagechinesename,@wageenglishname,@default
while @@fetch_status=0
begin
set @sql_string='alter table RL_wageStruc add '+@wagechinesename+' varchar(200),'+@wageenglishname+' varchar(200) default '+cast(@default as varchar)
print @sql_string
exec(@sql_string)
fetch next from cur_for into @wagechinesename,@wageenglishname,@default
end
close cur_for
deallocate cur_for
declare @wagechinesename varchar(200),@wageenglishname varchar(200),@default int
declare @sql_string varchar(8000)
declare cur_for cursor for
select wagechinesename,wageenglishname,[default] from RL_wageItem
open cur_for
fetch next from cur_for into @wagechinesename,@wageenglishname,@default
while @@fetch_status=0
begin
set @sql_string='alter table RL_wageStruc add '+@wagechinesename+' varchar(200),'+@wageenglishname+' varchar(200) default '+cast(@default as varchar)
exec(@sql_string)
fetch next from cur_for into @wagechinesename,@wageenglishname,@default
end
close cur_for
deallocate cur_for
下面的SP可作参考:SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE Proc SP_CreateReportTable(@ReportID int )
asSET NOCOUNT ON
declare @RptFieldName_en varchar(50) --字段名
declare @FieldDataType varchar(4) --N是decimal型,C是varchar型
declare @FieldSize int --数据类型的长度
declare @DecimalDigits int --decimal型小数位数
declare @field varchar(100)
declare @sql Varchar(8000)set @ReportID=rtrim(ltrim(@ReportID))declare ReportField_Cur cursor FORWARD_ONLY READ_ONLY
for
select RptFieldName_en,FieldDataType,FieldSize,DecimalDigits from ReportField where Report_ID=@ReportID order by SortOPEN ReportField_CurFETCH NEXT FROM ReportField_Cur
INTO @RptFieldName_en, @FieldDataType, @FieldSize, @DecimalDigits set @sql=''WHILE @@FETCH_STATUS = 0
BEGIN
if (@FieldDataType='N') --N是decimal型
begin
set @field=@RptFieldName_en +' decimal('+cast(@FieldSize as varchar(3))+','+cast(@DecimalDigits as varchar(3))+')'
end
else --C是varchar型
begin
set @field=@RptFieldName_en +' varchar(' + cast(@FieldSize as varchar(3)) + ')'
end
set @sql=@sql+@field+',' FETCH NEXT FROM ReportField_Cur
INTO @RptFieldName_en, @FieldDataType, @FieldSize, @DecimalDigits ENDCLOSE ReportField_Cur
DEALLOCATE ReportField_Curset @sql='Create Table ' +(select rtrim(ltrim(ReportName_en)) from ReportMain where Report_ID=@ReportID) +'('+left(@sql,len(@sql)-1)+')'Exec (@sql)GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO