動態給表新增刪除欄位並同時修改它的插入更新儲存過程

有一個表，使用者需要在後臺操作它，希望能對它動態進行新增刪除欄位。這個功能也許沒有問題，但是它原有插入與更新的兩個儲存過程，也需要一起修改。因此Insus.NET實現了它，因此此文會讓你瞭解到怎樣動態為一個表新增刪除欄位以及動態修改它的儲存過程。

首先需要建一個表[A]，這個表只有兩個欄位，一個是[ID]自動增長,另一個是表[B]的欄位名，儲存的每一筆記錄，即是使用者需要操作的表[B]的欄位。這個表[A]需要建新增，更新，以及刪除的儲存過程，方便使用者在後臺方便操作，還有重點部分，需要寫觸發器。如有記錄對錶[A]進行新增，更新或是刪除時，它會觸發去作表[B]相應操作，還要去修改表[B]的儲存過程。

動態修改表[B]的儲存過程：

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]的插入觸發器：

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]刪除觸發器：

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