当前位置： 移动技术网 > IT编程>数据库>MSSQL > 动态给表添加删除字段并同时修改它的插入更新存储过程

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

2017年12月12日  | 移动技术网IT编程  | 我要评论

樱花花海图片,龙城岁月片尾曲,路易帝都

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

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网