欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sqlserver对字段的添加修改删除、以及字段的说明

程序员文章站 2023-12-14 20:16:04
复制代码 代码如下: --新增表字段 alter procedure [dbo].[sp_web_tablefiled_insert] ( @tablename varch...
复制代码 代码如下:

--新增表字段
alter procedure [dbo].[sp_web_tablefiled_insert]
(
@tablename varchar(100),
@fieldname varchar(100),
@fieldexplain varchar(200),
@datatype varchar(100),
@connecttablename varchar(100),
@fieldlength int,
@newsid int output
)
as
begin transaction mytran
declare @errorsum int
if not exists (select * from syscolumns where id=object_id(@tablename) and name=@fieldname)
begin
insert tb_tablefield
(
tablename,
fieldname,
fieldexplain,
datatype,
connecttablename,
fieldlength,
usersetsign
)
values
(
@tablename,
@fieldname,
@fieldexplain,
@datatype,
@connecttablename,
@fieldlength,
'1'
)
declare @sql varchar(8000)
--判断类型
if(@datatype='decimal')
begin
set @sql = 'alter table ' + @tablename +' add ' + @fieldname +' ' + @datatype +'(' +convert(varchar,@fieldlength)+',2'+')'
end
else if(@datatype='varchar')
begin
set @sql = 'alter table ' + @tablename +' add ' + @fieldname +' ' + @datatype +'(' +convert(varchar,@fieldlength)+')'
end
else
begin
set @sql = 'alter table ' + @tablename +' add ' + @fieldname +' ' + @datatype
end
exec(@sql)
execute sp_addextendedproperty n'ms_description', @fieldexplain, n'user', n'dbo', n'table', @tablename, n'column' , @fieldname;
set @errorsum=@errorsum+@@error
set @newsid=0;
end
else
begin
set @newsid=1;
end
if(@errorsum>0)
begin
rollback tran
end
else
begin
commit tran mytran
end
--修改表字段
alter procedure [dbo].[sp_web_tablefiled_update]
(
@tablename varchar(100),
@fieldname varchar(100),
@fieldexplain varchar(200),
@datatype varchar(100),
@connecttablename varchar(100),
@fieldlength int,
@id int,
@newsid int output
)
as
begin transaction mytran
declare @fname varchar(100)
declare @errorsum int
--先取出表中以前的字段名称
select @fname=fieldname from tb_tablefield where id=@id
declare @pstid int
declare @sql varchar(8000)
--再根据字段名称取出tb_paysystemtolocation中对应的id
select @pstid=id from tb_paysystemtolocation where locationfield=@fname
set @sql = 'sp_rename '+char(39)+@tablename+'.['+@fname+']'+char(39)+',' +char(39)+@fieldname+char(39)+',' + char(39)+'column' +char(39)
exec(@sql)
update tb_tablefield
set tablename=@tablename,
fieldname=@fieldname,
fieldexplain=@fieldexplain,
datatype=@datatype,
connecttablename=@connecttablename,
fieldlength=@fieldlength
where id=@id
--修改字段说明
execute sp_updateextendedproperty n'ms_description', @fieldexplain, n'user', n'dbo', n'table', @tablename, n'column' , @fieldname;
--exec sp_updateextendedproperty 'ms_description',@fieldexplain,'user',dbo,'table',@tablename,'column',@fieldname
set @newsid=0;
set @errorsum=@errorsum+@@error
if(@@error>0)
begin
rollback tran
end
else
begin
commit tran mytran
end
-删除表字段
alter procedure [dbo].[sp_web_tablefiled_delete]
(
@id int,
@newsid int output
)
as
begin transaction mytran
declare @fname varchar(100)
declare @tablename varchar(100)
declare @pstid int
declare @sql varchar(8000)
declare @errorsum int
--取出字段名,表名
select @fname=fieldname,@tablename=tablename from tb_tablefield where id=@id
--取出tb_paysystemtolocation的id
select @pstid=id from tb_paysystemtolocation where locationfield=@fname
delete from tb_tablefield where id=@id
set @sql='alter table ' +@tablename+ ' drop column '+ @fname
exec(@sql)
set @errorsum=@errorsum+@@error
set @newsid=0;
if(@errorsum>0)
begin
rollback tran
end
else
begin
commit tran mytran
end

上一篇:

下一篇: