--新增表字段 ALTER procedure [dbo].[sp_Web_TableFiled_Insert] ( @TableName varchar(100), @FieldName varchar(100), @FieldExplain varchar(200), @DataType varchar(100), @ConnectTableName varchar(100), @FieldLength int, @NewsID int output ) as begin transaction mytran declare @errorSum int if not exists (SELECT * FROM syscolumns where id=object_id(@TableName) AND name=@FieldName) begin insert tb_TableField ( TableName, FieldName, FieldExplain, DataType, ConnectTableName, FieldLength, UserSetSign ) values ( @TableName, @FieldName, @FieldExplain, @DataType, @ConnectTableName, @FieldLength, '1' ) declare @sql varchar(8000) --判断类型 if(@DataType='decimal') begin set @sql = 'alter table ' + @TableName +' add ' + @FieldName +' ' + @DataType +'(' +Convert(varchar,@FieldLength)+',2'+')' end else if(@DataType='varchar') begin set @sql = 'alter table ' + @TableName +' add ' + @FieldName +' ' + @DataType +'(' +Convert(varchar,@FieldLength)+')' end else begin set @sql = 'alter table ' + @TableName +' add ' + @FieldName +' ' + @DataType end exec(@sql) EXECUTE sp_addextendedproperty N'MS_Description', @FieldExplain, N'user', N'dbo', N'Table', @TableName, N'column' , @FieldName; set @errorSum=@errorSum+@@error set @NewsID=0; end else begin set @NewsID=1; end if(@errorSum>0) begin rollback tran end else begin commit tran mytran end --修改表字段 ALTER procedure [dbo].[sp_API_TableFiled_Update] ( @TableName varchar(100), @FieldName varchar(100), @FieldExplain varchar(200), @DataType varchar(100), @ConnectTableName varchar(100), @FieldLength int, @PaySystemField varchar (100), @ID int, @NewsID int output ) as begin transaction mytran begin try declare @fname varchar(100) declare @filetype varchar(20) declare @tsql varchar(1000) ---先取出表中以前的字段名称 select @fname=FieldName,@filetype=DataType from tb_TableField where ID=@ID declare @pstid int declare @sql varchar(8000) --再根据字段名称取出tb_PaySystemToLocation中对应的ID select @pstid=ID from tb_PaySystemToLocation where LocationField=@fname if(@filetype!=@DataType)--如果字段类型修改了 begin if(@DataType='decimal') begin set @tsql = 'alter table ' + @TableName +' add ' + @FieldName +' ' + @DataType +'(' +Convert(varchar,@FieldLength)+',2'+')' end else if(@DataType='varchar') begin set @tsql = 'alter table ' + @TableName +' add ' + @FieldName +' ' + @DataType +'(' +Convert(varchar,@FieldLength)+')' end else begin set @tsql = 'alter table ' + @TableName +' add ' + @FieldName +' ' + @DataType end declare @delsql varchar(1000) set @delsql='ALTER TABLE ' +@tablename+ ' DROP COLUMN '+ @fname exec(@delsql)--先删除旧字段 再添加新字段 exec(@tsql) EXECUTE sp_addextendedproperty N'MS_Description', @FieldExplain, N'user', N'dbo', N'Table', @TableName, N'column' , @FieldName; end if(@fname!=@FieldName) begin set @sql = 'sp_rename '+CHAR(39)+@TableName+'.['+@fname+']'+CHAR(39)+',' +char(39)+@FieldName+char(39)+',' + char(39)+'COLUMN' +CHAR(39) exec(@sql) end update tb_TableField set TableName=@TableName, FieldName=@FieldName, FieldExplain=@FieldExplain, DataType=@DataType, ConnectTableName=@ConnectTableName, FieldLength=@FieldLength where ID=@ID --修改字段说明 EXECUTE sp_updateextendedproperty N'MS_Description', @FieldExplain, N'user', N'dbo', N'Table', @TableName, N'column' , @FieldName; update tb_PaySystemToLocation set PaySystemField=@PaySystemField,LocationField=@FieldName,TableName=@TableName where ID=@pstid set @NewsID=0 commit tran mytran end try begin catch insert into tb_error (Message)values(ERROR_MESSAGE()) set @NewsID=1 if(@@TRANCOUNT >0) rollback transaction mytran end catch -删除表字段 ALTER procedure [dbo].[sp_Web_TableFiled_Delete] ( @ID int, @NewsID int output ) as begin transaction mytran declare @fname varchar(100) declare @tablename varchar(100) declare @pstid int declare @sql varchar(8000) declare @errorSum int --取出字段名,表名 select @fname=FieldName,@tablename=TableName from tb_TableField where ID=@ID --取出tb_PaySystemToLocation的ID select @pstid=ID from tb_PaySystemToLocation where LocationField=@fname delete from tb_TableField where ID=@ID set @sql='ALTER TABLE ' +@tablename+ ' DROP COLUMN '+ @fname exec(@sql) set @errorSum=@errorSum+@@error set @NewsID=0; if(@errorSum>0) begin rollback tran end else begin commit tran mytran end
如果您也喜欢它,动动您的小指点个赞吧