本帖最后由 guo9long 于 2010-8-28 10:46 编辑
由于都是采用纯脚本形式进行,所以很多都采用脚本的形式实现。当然也可以手工一个一个添加,那样不便于后期维护。
以下的代码用于向数据库添加物理字段,添加模板表比较类似。
create procedure AddForTable --创建添加字段的存储过程
@TableName varchar(30),
@FieldName varchar(30),
@DataType varchar(100)
as
Declare @Sql varchar(3000)
Set @Sql='Alter Table '+@TableName+' Add '+ @FieldName + ' '+ @DataType
IF NOT EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID(@TableName) and name=@FieldName)--检测重复性
exec (@sql)
GO
------------批增加字段------------------------------------------------------
Create procedure BAddForTable--这里是为多张表 表头和表体需要添加相同的字段而建立。
@TableName varchar(30),
@TableName2 varchar(30)
as
if @TableName is not null
Begin
exec AddForTable @TableName,'FMachineID','int'
exec AddForTable @TableName,'FWorkGroupID','int'
exec AddForTable @TableName,'FSEOrder_PO','nvarchar(50)'
exec AddForTable @TableName,'FSEOrder_PONO','nvarchar(50)'
exec AddForTable @TableName,'FInputTime', 'Datetime' --制单时间
exec AddForTable @TableName,'FCheckTime','Datetime' --审核时间
end
exec AddForTable @TableName2,'FBatchWidth','decimal(28,10)'
exec AddForTable @TableName2,'FLengthPerRoll','decimal(28,10)'
exec AddForTable @TableName2,'FBatchPack','int'
exec AddForTable @TableName2,'FRollPerPack','int'
exec AddForTable @TableName2,'FBatchRoll','int'
exec AddForTable @TableName2,'FBatchLength','decimal(28,10)'
exec AddForTable @TableName2,'FRollNo','varchar(30)'
exec AddForTable @TableName2,'FBatchLabel','varchar(30)'
exec AddForTable @TableName2,'FBatchKZ','decimal(28,10)'
exec AddForTable @TableName2,'FSecPrice','decimal(28,10)'
exec AddForTable @TableName2,'FSecTaxPrice','decimal(28,10)'
exec AddForTable @TableName2,'FQcGrade','int default(0)' --添加质量等级字段
.
.
.
.
GO
EXEC BAddForTable 'ICStockBill','ICStockBillEntry'
EXEC BAddForTable 'ICStockBill_1','ICStockBillEntry_1'
EXEC BAddForTable 'ICStockBill_2','ICStockBillEntry_2'
EXEC BAddForTable 'ICStockBill_5','ICStockBillEntry_5'
EXEC BAddForTable 'ICStockBill_10','ICStockBillEntry_10'
.
.
--仅供参考,如有错误或者更好的方法,请指出!
Drop procedure BAddForTable
Drop procedure AddForTable |