Dynamically add a Delete field to a table and modify its insert update stored procedure _mssql

There is a table where the user needs to manipulate it in the background, hoping to add a delete field to it dynamically. This feature may not be a problem, but it was originally inserted with the updated two stored procedures, also need to be modified together. So insus.net implements it, so this article will give you an idea of how to dynamically add delete fields to a table and dynamically modify its stored procedures

First you need to build a table [a], this table has only two fields, one is [ID] grow automatically, the other is the field name of table [b], every record stored, that is, the user needs to manipulate the table [b] field. This table [A] needs to be built to add, update, and delete stored procedures to facilitate the user in the background to facilitate the operation, there are key parts, need to write triggers. If a record is added to table [A], updated or deleted, it triggers the action on table [B] and modifies the stored procedure in table [B].

To dynamically modify a stored procedure for table [B]:

Copy Code code 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 (18,4) '--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 [A]:

Copy Code code 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] Delete triggers:

Copy Code code 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