有一個表，用戶需要在后臺操作它，希望能對它動態(tài)進行添加刪除字段。這個功能也許沒有問題，但是它原有插入與更新的兩個存儲過程，也需要一起修改。因此Insus.NET實現(xiàn)了它，因此此文會讓你了解到怎樣動態(tài)為一個表添加刪除字段以及動態(tài)修改它的存儲過程
首先需要建一個表[A]，這個表只有兩個字段，一個是[ID]自動增長,另一個是表[B]的字段名，存儲的每一筆記錄，即是用戶需要操作的表[B]的字段。這個表[A]需要建添加，更新，以及刪除的存儲過程，方便用戶在后臺方便操作，還有重點部分，需要寫觸發(fā)器。如有記錄對表[A]進行添加，更新或是刪除時，它會觸發(fā)去作表[B]相應(yīng)操作，還要去修改表[B]的存儲過程。
動態(tài)修改表[B]的存儲過程：
復(fù)制代碼 代碼如下:
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)' --動態(tài)的字段數(shù)據(jù)類型都一樣
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]的插入觸發(fā)器：
復(fù)制代碼 代碼如下:
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]刪除觸發(fā)器：
復(fù)制代碼 代碼如下:
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
