自动建表 2

/*
declare @Name varchar(20)
set @Name = ''
exec dbo.p_CreateYXRZTable @s_tablename='WWdcctrl12',@int=30,@dec=5,@strSm=0,@strMi=0,@strBig=0,@text=0,@message = @Name out
select @Name

*/

CREATE proc dbo.p_CreateYXRZTable
@s_tablename varchar(100),
@int int = null,
@dec int = null,
@strSm int = null,
@strMi int = null,
@strBig int = null,
@text int = null,
@Message int=null Output
as
/*---------------------------------------------------------------------------------------------------------------
----------过程传入的是一种数据类型的个数,@s_用来存储新增列的信息
---------------------------------------------------------------------------------------------------------------*/
declare @s_int varchar(8000),@s_Decimal varchar(8000),@s_StrSm varchar(8000),@s_StrMi varchar(8000),@s_StrBig varchar(8000),@s_Text varchar(8000),@s_defultB varchar(1000),@s_defultE varchar(1000)
declare @i int,@iFlag int
select @s_Decimal = '',@s_int = '',@s_StrSm = '',@s_StrMi = '',@s_StrBig = '',@s_Text = '',@s_defultB = '',@s_defultE = ''
select @int = isnull(@int,0),@dec = isnull(@dec,0),@strSm = isnull(@strSm,0),@strMi = isnull(@strMi,0),@Text = isnull(@Text,0),@strBig = isnull(@strBig,0)

--生成数据表名称
set @s_tablename='tbYXRZ' + @s_tablename
--判断目标数据库是否已经存在要生成的数据表,在没有重名的情况下进行新加表处理
if not exists(select 1 from sysobjects where id=object_id(@s_tablename) and type='u')
begin 
--表头表尾字段处理
set @s_defultB = 'CREATE TABLE ['+ @s_tablename +'] (
 [yxID] [int] IDENTITY (1, 1) NOT NULL ,
 [reportDate] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [jz] [int] NULL ,
 [bz] [int] NULL ,
 [bc] [int] NULL ,
 [tq] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [zbry] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
 [jjman1] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [jjtime1] [datetime] NULL ,
 [jjman2] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [jjtime2] [datetime] NULL ,
 [CreateTime] [datetime] NULL ,
 [modifyman] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [modifyTime] [datetime] NULL , 
        [Data] [image] NULL ,
 [yxjs] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 [jdsx] [text] COLLATE Chinese_PRC_CI_AS NULL ,'

set @s_defultE = 'CONSTRAINT [PK_'+@s_tablename+'] PRIMARY KEY  CLUSTERED
 (
  [yxID] DESC
 )  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
--------------------
--数据表字段语句生成
--------------------
set @i=1
while @int >= @i begin
  set @s_int = @s_int + '[Int' + right(1000 + @i,3) + '] [int] NULL ,'
  set @i = @i + 1
end

set @i=1
while @dec >= @i begin
  set @s_Decimal = @s_Decimal + '[Dec' + right(1000 + @i,3) + '] [decimal](18, 4) NULL ,'
  set @i = @i + 1
end

set @i=1
while @strSm >= @i begin
  --set @s_StrSm = @s_StrSm + '[StrSm' + right(1000 + @i,3) + '] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,'
  set @s_StrSm = @s_StrSm + '[StrSm' + right(1000 + @i,3) + '] [varchar] (10) ,'
  set @i = @i + 1
end

set @i=1
while @strMi >= @i begin
  --set @s_StrMi = @s_StrMi + '[StrMi' + right(1000 + @i,3) + '] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,'
  set @s_StrMi = @s_StrMi + '[StrMi' + right(1000 + @i,3) + '] [varchar] (50) ,'
  set @i = @i + 1
end

set @i=1
while @strBig >= @i begin
  --set @s_StrBig = @s_StrBig + '[StrBig' + right(1000 + @i,3) + '] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,'
  set @s_StrBig = @s_StrBig + '[StrBig' + right(1000 + @i,3) + '] [varchar] (500) ,'
  set @i = @i + 1
end

set @i=1
while @text >= @i begin
  set @s_Text = @s_Text + '[Text' + right(1000 + @i,3) + '] [text] COLLATE Chinese_PRC_CI_AS NULL ,'
  set @i = @i + 1
end
--------------------
--数据表创建
--------------------
exec(@s_defultB + @s_int + @s_Decimal + @s_StrSm + @s_StrMi + @s_StrBig + @s_Text + @s_defultE)
end
else
--@message=1表示数据库中已经存在同名数据表
  set @message=1

 

GO