动态给表添加删除字段并同时修改它的插入更新存储过程

有一个表，用户需要在后台操作它，希望能对它动态进行添加删除字段。这个功能也许没有问题，但是它原有插入与更新的两个存储过程，也需要一起修改。因此Insus.NET实现了它，因此此文会让你了解到怎样动态为一个表添加删除字段以及动态修改它的存储过程。

首先需要建一个表[A]，这个表只有两个字段，一个是[ID]自动增长,另一个是表[B]的字段名，存储的每一笔记录，即是用户需要操作的表[B]的字段。这个表[A]需要建添加，更新，以及删除的存储过程，方便用户在后台方便操作，还有重点部分，需要写触发器。如有记录对表[A]进行添加，更新或是删除时，它会触发去作表[B]相应操作，还要去修改表[B]的存储过程。

动态修改表[B]的存储过程：

View Code

CREATE  PROCEDURE  [ dbo ]. [ usp_B_DymanicallyAlterStoreProcedure ]
AS
     DECLARE  @VariableList  NVARCHAR( MAX)  =  ''  
     DECLARE  @FieldList  NVARCHAR( MAX)  =  '' 
     DECLARE  @ValueList  NVARCHAR( MAX)  =  ''  
     DECLARE  @FieldValueList  NVARCHAR( MAX)  =  ''
    
     DECLARE  @I  INT  =  1,  @R  INT  =  0
     SET  @R  = ( SELECT  MAX( [ Id ])  FROM  [ dbo ]. [ A ])
     WHILE ( @I  <=  @R)
     BEGIN
         DECLARE  @fName  NVARCHAR( 100)
         IF  EXISTS( SELECT  [ Id ]  FROM  [ dbo ]. [ A ]  WHERE  [ Id ]  =  @I)
         BEGIN
             SELECT  @fName  =  [ FieldName ]  FROM  [ dbo ]. [ A ]  WHERE  [ Id ]  =  @I
             SET  @VariableList  =  @VariableList  +  ' ,@ '  +  @fName  + '  DECIMAL(18,4) '  -- 动态的字段数据类型都一样
             SET  @FieldList  =   @FieldList  +  ' ,[ '  +  @fName  +  ' ] '
             SET  @ValueList  =  @ValueList  +  ' ,@ '  +  @fName
             SET  @FieldValueList  =  @FieldValueList  +  ' ,[ '  +  @fName  +  ' ] = @ '  +  @fName
         END
         SET  @I  =  @I  +  1
     END

     DECLARE  @sql_I  NVARCHAR( MAX), @sql_U  NVARCHAR( MAX)
     SET  @sql_I  =  '
    ALTER PROCEDURE [dbo].[usp_B_Insert]
    (
        @ItemCode NVARCHAR(50) 
         ' +  @VariableList  + '
    )
    AS    
    INSERT INTO [dbo].[B] ([ItemCode] ' +  @FieldList  + ' ) VALUES (@ItemCode ' +  @ValueList  + ' )
     '
     EXECUTE sp_EXECUTESQL  @sql_I;
    
     SET  @sql_U  =  '
    ALTER PROCEDURE [dbo].[usp_B_Update]
    (
        @Id INT,
        @ItemCode NVARCHAR(50)
         ' +  @VariableList  + '
    )
    AS    
    UPDATE [dbo].[B] SET [ItemCode] = @ItemCode ' +  @FieldValueList  + '  WHERE [Id] = @Id
     '
     EXECUTE sp_EXECUTESQL  @sql_U;

 

表[A]的插入触发器：

View Code

CREATE  TRIGGER  [ dbo ]. [ tri_A_Insert ]  ON  [ dbo ]. [ A ]
FOR  INSERT
AS
BEGIN
     SET NOCOUNT  ON
     DECLARE  @FieldName  NVARCHAR( 50) 
     SELECT  @FieldName  =  [ FieldName ]  FROM INSERTED

     EXECUTE( ' IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID( '' B '' ) AND [name] =  ''' +  @FieldName  + ''' )    
    ALTER TABLE [B] ADD [ ' +  @FieldName  + ' ] DECIMAL(18,4) NULL ')

     EXECUTE  [ dbo ]. [ usp_B_DymanicallyAlterStoreProcedure ];
END

 

表[A]删除触发器：

View Code

CREATE  TRIGGER  [ dbo ]. [ tri_A_Delete ]  ON  [ dbo ]. [ A ]
FOR  DELETE
AS
BEGIN
     SET NOCOUNT  ON
     DECLARE  @FieldName  NVARCHAR( 50) 
     SELECT   @FieldName  =  [ FieldName ]  FROM DELETED

     EXECUTE( ' IF EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID( '' B '' ) AND [name] =  ''' +  @FieldName  + ''' )    
    ALTER TABLE [B] DROP COLUMN [ ' +  @FieldName  + ' ] ')
    
     EXECUTE  [ dbo ]. [ usp_B_DymanicallyAlterStoreProcedure ];
END