服务器之家

服务器之家 > 正文

动态给表添加删除字段并同时修改它的插入更新存储过程

时间:2019-12-11 14:24     来源/作者:MSSQL教程网

有一个表,用户需要在后台操作它,希望能对它动态进行添加删除字段。这个功能也许没有问题,但是它原有插入与更新的两个存储过程,也需要一起修改。因此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 

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
配置IIS网站web服务器的安全策略配置解决方案
配置IIS网站web服务器的安全策略配置解决方案 2019-05-23
Nginx服务器究竟是怎么执行PHP项目
Nginx服务器究竟是怎么执行PHP项目 2019-05-24
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
返回顶部