導(dǎo)讀:1建站知識有一個(gè)表，用戶需要在后臺操作它，希望能對它動態(tài)進(jìn)行添加刪除字段網(wǎng)站建設(shè)哪家好網(wǎng)站seo優(yōu)化診斷工具。

有一個(gè)表，用戶需要在后臺操作它，希望能對它動態(tài)進(jìn)行添加刪除字段。這個(gè)功能也許沒有問題，但是它原有插入與更新的兩個(gè)存儲過程，也需要一起修改。因此Insus.NET實(shí)現(xiàn)了它，因此此文會讓你了解到怎樣動態(tài)為一個(gè)表添加刪除字段以及動態(tài)修改它的存儲過程 首先需要建一個(gè)表[A]，這個(gè)表只有兩個(gè)字段，一個(gè)是[ID]自動增長,另一個(gè)是表[B]的字段名，存儲的每一筆記錄，即是用戶需要操作的表[B]的字段。這個(gè)表[A]需要建添加，更新，以及刪除的存儲過程，方便用戶在后臺方便操作，還有重點(diǎn)部分，需要寫觸發(fā)器。如有記錄對表[A]進(jìn)行添加，更新或是刪除時(shí)，它會觸發(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 NVARCHARseo網(wǎng)站優(yōu)化(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;

復(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

復(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(''seo網(wǎng)站關(guān)鍵詞優(yōu)化B'') AND [name] = '''+ @FieldName +''') ALTER TABLE [B] DROP COLUMN ['+ @Fiel建設(shè)網(wǎng)站公司dName +']') EXECUTE [dbo].[usp_B_DymanicallyAlterStoreProcedure]; END

關(guān)鍵詞標(biāo)簽: 字段 它的