一个可调用某表新增日期列的存储过程

 

 

-->建测试表
  create table T1(id int identity,[2009-03-06] varchar(10))
-->创建存储过程 
create proc sp_test1
@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=(select count(1) from sysobjects where id<=a.id)
        from sysobjects a ) t
  where fid<11

if 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_test1 't1'

-->查看结果
select * from t1
/*
id          2009-03-06 2009-03-07 2009-03-08 2009-03-09 2009-03-10 2009-03-11 2009-03-12 2009-03-13 2009-03-14 2009-03-15 2009-03-16
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

(10 行受影响)
*/