/****** Object: Stored Procedure dbo.ChangeField Script Date: 1998-05-02 19:15:18 ******/
CREATE PROCEDURE ChangeField @ok bit=0 output AS
declare @alterSQL NVARCHAR(200)
declare @name char(20)
declare @nameOld char(20) --原名
declare @type char(20)
declare @len smallint
declare @del bit
declare cur_str cursor for
select Aname,Aname_Old ,Atype,alen,deltag from gz_stru where authority<>'固定' order by s_id
open cur_str
fetch next from cur_str into @name,@nameOld,@type,@len,@del
while @@FETCH_STATUS=0
begin
--删除字段
if @del=1
begin
SET @alterSQL='Alter table gz_data drop column '+ltrim(rtrim(@nameold))
exec sp_executesql @altersql
fetch next from cur_str into @name,@nameOld,@type,@len,@del
continue
end
--字段信息
IF @type='字符'
set @type='char('+ltrim(rtrim(cast(@len as char(5))))+')'
IF @type='数字'
set @type='[decimal]('+ltrim(rtrim(cast(@len as char(5))))+',2)'
IF @type='整形'
set @type='int'
--判断是否已经存在
select * from syscolumns c,sysobjects o where c.id=o.id and o.name='gz_data' and c.name=ltrim(rtrim(@nameOld))
if @@rowcount=0 --新增
SET @alterSQL='Alter table gz_data add '+ltrim(rtrim(@name))+' '+@type+' NULL'
else
begin
if @name<>@nameOld --先处理修改字段名的信息
begin
set @altersql='Sp_rename "gz_data.['+rtrim(@nameold)+']","'+rtrim(@name)+'","column"'
select ltrim(rtrim(@altersql))
exec sp_executesql @altersql --!!!就是这个系统存储过程sp_executesql--
end
set @altersql='alter table gz_data alter column '+ltrim(rtrim(@name))+' '+@type
end
select @altersql
exec sp_executesql @altersql
fetch next from cur_str into @name,@nameOld,@type,@len,@del
end
delete from gz_stru where deltag=1
if @@error>0
set @ok=0 --运行失败
else
set @ok=1
deallocate cur_str
SELECT @OK
GO