动态给表添加删除字段并同时修改它的插入更新存储过程
时间:2024-01-17 22:12:56
有一个表,用户需要在后台操作它,希望能对它动态进行添加删除字段。这个功能也许没有问题,但是它原有插入与更新的两个存储过程,也需要一起修改。因此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
标签:删除字段
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
conda虚拟环境默认路径的修改方法
2022-07-02 03:14:50
![](https://img.aspxhome.com/file/2023/4/124724_0s.png)
webpack vue 项目打包生成的文件,资源文件报404问题的修复方法(总结篇)
2024-04-27 16:10:21
![](https://img.aspxhome.com/file/2023/8/132968_0s.png)
NumPy对数组按索引查询实战方法总结
2021-02-23 06:33:18
![](https://img.aspxhome.com/file/2023/9/107219_0s.png)
Python实战之能监控文件变化的神器—看门狗
2022-01-05 22:01:28
![](https://img.aspxhome.com/file/2023/3/95423_0s.jpg)
python中pivot()函数基础知识点
2023-12-18 15:28:06
python实现五子棋算法
2022-12-22 18:53:15
![](https://img.aspxhome.com/file/2023/4/133444_0s.gif)
如何在windows下安装Pycham2020软件(方法步骤详解)
2023-05-19 14:07:30
![](https://img.aspxhome.com/file/2023/0/89300_0s.png)
对Python+opencv将图片生成视频的实例详解
2022-01-03 08:42:16
图文详解在Anaconda安装Pytorch的详细步骤
2022-12-27 10:37:25
![](https://img.aspxhome.com/file/2023/8/103758_0s.png)
Go缓冲channel和非缓冲channel的区别说明
2024-05-22 10:11:01
Php+SqlServer实现分页显示
2023-11-20 05:02:24
MySQL ERROR 1045 (28000) 错误的解决办法
2024-01-16 18:49:06
![](https://img.aspxhome.com/file/2023/3/121273_0s.png)
在python中获取div的文本内容并和想定结果进行对比详解
2021-09-05 07:29:37
Javascript中数组方法汇总(推荐)
2023-07-16 08:33:02
MySQL数据库事务与锁深入分析
2024-01-28 19:03:12
python使用正则筛选信用卡
2021-03-10 14:06:47
![](https://img.aspxhome.com/file/2023/0/66770_0s.png)
Django的get_absolute_url方法的使用
2023-05-28 02:38:14
![](https://img.aspxhome.com/file/2023/9/92369_0s.png)
[译]艺术和设计的差异 (1)
2009-09-25 12:38:00
网站508规范(译)
2008-04-03 13:26:00
![](https://img.aspxhome.com/file/UploadPic/20084/3/gobuttons_85s.jpg)
python opencv实现证件照换底功能
2021-06-22 07:17:58
![](https://img.aspxhome.com/file/2023/5/128755_0s.jpg)