Dynamically add and delete a field to a table and modify its stored procedure for insert and update.

There is a table that you need to operate on in the background and want to dynamically add and delete fields to it. This feature may be fine, but it also needs to be modified together with the original two stored procedures for insertion and update. Therefore, Insus. NET implements it. Therefore, this article will show you how to dynamically add and delete fields to a table and modify its stored procedures.

First, you need to create A table [A]. This table has only two fields, one is the automatic growth of [ID], the other is the field name of the table [B], each record stored, this is the field of table [B] to be operated by the user. This table [A] contains stored procedures that need to be created, updated, and deleted, so that users can conveniently operate in the background. There are also key parts that need to be written as triggers. If A record is added, updated, or deleted to table [A], it triggers the corresponding operation for table [B] and modifies the stored procedure of table [B.

Modify the stored procedure of table [B] dynamically:Copy codeThe Code is as follows: 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 () '-- dynamic field data types are the same
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;

Insert trigger for table [:Copy codeThe Code is as follows: 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

Table [A] deletion trigger:Copy codeThe Code is as follows: 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