-->建测试表 create table T1(id int identity,[2009-03-06] varchar(10)) -->创建存储过程 alter proc sp_test @tb varchar(200) as declare @dt table(a varchar(10)) insert @dt select convert(varchar(10),dateadd(day,fid-1,getdate()),120) from (select fid=1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10) tif not exists(select 1 from syscolumns where id=object_id(@tb) and name in (select a from @dt)) begin declare @s nvarchar(4000) set @s='' select @s=@s +'alter table '+ @tb +' add '+ quotename(a) + ' varchar(10);' from @dt exec(@s) end go -->调用过程 exec sp_test 't1'-->查看结果 select * from t1
create procedure @tablename varchar(50) as begin declare @strs nvarchar(4000) set @strs='' select @strs=@strs +'alter table '+ @tablename +' add '+列名 + ' varchar(10);' from @dt exec(@s) end go
不用非存储过程,写一段SQL也可。如:
exec ('alter table '+ 文本框中表名 +' add col1 int')
create table T1(id int identity,[2009-03-06] varchar(10))
-->创建存储过程
alter proc sp_test
@tb varchar(200)
as
declare @dt table(a varchar(10))
insert @dt
select convert(varchar(10),dateadd(day,fid-1,getdate()),120)
from (select fid=1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10) tif not exists(select 1 from syscolumns where id=object_id(@tb)
and name in (select a from @dt))
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s +'alter table '+ @tb +' add '+ quotename(a) + ' varchar(10);'
from @dt
exec(@s)
end
go
-->调用过程
exec sp_test 't1'-->查看结果
select * from t1
我写的那个T1并不是一个固定的T1表啊~是从文本框中抓取的一个表的名字,可能异于T1这部分应该怎么写呢?
exec sp_test '文本框中的表名'
@tablename varchar(50)
as
begin
declare @strs nvarchar(4000)
set @strs=''
select @strs=@strs +'alter table '+ @tablename +' add '+列名 + ' varchar(10);'
from @dt
exec(@s)
end
go