升星——公布自动生成测试数据存储过程——昨夜小楼,该如何处理
升星——公布自动生成测试数据存储过程——昨夜小楼
- SQL code
create procedure cs
@input varchar(max)
as
set nocount on
if patindex('%[@#$]%',@input)=0 return
select @input=replace(@input,' ',' '),@input=ltrim(rtrim(@input))
select top 94 code=identity(tinyint,33,1),m=cast(null as varchar(2)),w=cast(null as varchar(1)) into # from syscolumns
select @input=replace(@input collate chinese_prc_cs_as_ks_ws,nchar(code+65248),char(code)) from #
truncate table #
insert into # select char(13),char(10)
insert into # select char(9) ,','
insert into # select ' ',','
insert into # select '|' ,','
insert into # select '¦' ,','
insert into # select '。','.'
insert into # select '·','.'
insert into # select char(39),''
select @input=replace(@input collate chinese_prc_cs_as_ks_ws,m,w) from #
drop table #
while patindex('%[^,][;]%',@input)>0 set @input=stuff(@input,patindex('%[^,][;]%',@input)+1,1,',;')
while patindex('%[;][^,]%',@input)>0 set @input=stuff(@input,patindex('%[;][^,]%',@input),1,';,')
set @input=replace(@input,';','null')
while charindex(',,',@input)>0 set @input=replace(@input,',,',',')
set @input=replace(@input,char(10)+',',char(10))
set @input=replace(@input,','+char(10),char(10))
while charindex(char(10)+char(10),@input)>0 set @input=replace(@input,char(10)+char(10),char(10))
if left(@input,1)=char(10) set @input=right(@input,len(@input)-1)
if right(@input,1)<>char(10) set @input=@input+char(10)
declare @tab sysname -- 表名：@=变量表；#=临时表；$=实体表
declare @tid tinyint
declare @yes bit
declare @cid smallint
declare @col varchar(1000)
declare @max smallint
declare @type sysname
declare @lenp smallint
declare @lens smallint
declare @sql varchar(8000)
declare @tabs table (id int identity,name sysname)
declare @data table (id int identity,data varchar(8000))
declare @temp table (id int,temp varchar(1000))
declare @code table (id int,code varchar(8000))
if charindex('$',@input)>0
begin
set @tab=substring(@input,charindex('$',@input)+1,charindex(char(10),@input,charindex('$',@input))-charindex('$',@input)-1)
if object_id(@tab) is not null
begin
raiserror(N'数据库中已存在名为 ''%s'' 的对象。',16,1,@tab)
return
end
end
while patindex('%[@#$]%',@input)>0
begin
select @tab=left(@input,charindex(char(10),@input)-1),@tid=isnull(@tid,0)+1,@yes=1,@cid=1,@input=right(@input,len(@input)-charindex(char(10),@input))
if left(@tab,1)='0' select @tab=stuff(@tab,1,1,''),@yes=0
if len(@tab)=1 set @tab=@tab+'T'+ltrim(@tid)
if left(@tab,1)='$' set @tab=quotename(stuff(@tab,1,1,''))
insert into @tabs values (@tab)
if @yes=0 set @col=null
else select @col=left(@input,charindex(char(10),@input)-1)+',',@input=right(@input,len(@input)-charindex(char(10),@input)),@col=replace(@col,',',':')
while charindex(char(10),@input)>0
begin
insert into @data select left(@input,charindex(char(10),@input)-1)
set @input=right(@input,len(@input)-charindex(char(10),@input))
if left(@input,1) in ('@','#','$') or left(@input,2) in ('0@','0#','0$') break
end
delete from @data where patindex('%[^,-]%',data)=0
select @max=max(len(data)-len(replace(data,',',''))) from @data
update @data set data=data+replicate(',null',@max-len(data)+len(replace(data,',','')))+','
set @max=isnull(len(@col)-len(replace(@col,':','')),0)
insert into @code select id,null from @data order by id
while exists (select 1 from @data where charindex(',',data)>0)
begin
insert into @temp select id,nullif(left(data,charindex(',',data)-1),'null') from @data order by id
update @data set data=right(data,len(data)-charindex(',',data))
if exists (select 1 from @temp a inner join @data b on a.id=b.id and a.temp is not null and left(b.data,4) not in ('','null')) and not exists (select 1 from @temp a inner join @data b on a.id=b.id and a.temp is not null and left(b.data,4) not in ('','null') and isdate(a.temp+space(1)+left(b.data,charindex(',',b.data)-1))=0)
begin
update a set a.temp=a.temp+space(1)+left(b.data,charindex(',',b.data)-1) from @temp a inner join @data b on a.id=b.id where a.temp is not null and left(b.data,charindex(',',b.data)-1)<>'null'
update @data set data=right(data,len(data)-charindex(',',data))
end
if not exists (select 1 from @temp where temp is not null) set @type=' sql_variant'
else if not exists (select 1 from @temp where temp is not null and isnumeric(temp)=0)
begin
if exists (select 1 from @temp where patindex('%[Ee]%',temp)>0) set @type=' float'
else if exists (select 1 from @temp where charindex('.',temp)>0)
begin
select @lenp=max(charindex('.',case when left(temp,1)='-' then right(temp,len(temp)-1) else temp end))-1,@lens=max(charindex('.',reverse(temp)))-1 from @temp where charindex('.',temp)>0
if @lenp+@lens>@@max_precision set @type=' float'
else set @type=' numeric('+ltrim(@lenp+@lens)+','+ltrim(@lens)+')'
end
else if exists (select 1 from @temp where len(temp)>1 and left(temp,1)='0') select @type=' varchar('+ltrim(max(datalength(temp)))+')' from @temp where temp is not null
else if exists (select 1 from @temp where temp is not null and len(temp)<>8 or isdate(temp)=0)
begin
select @lenp=isnull(max(len(temp)),0) from @temp where left(temp,1)<>'-'
select @lens=-isnull(max(len(temp)-1),0) from @temp where left(temp,1)='-'
if @lenp <= abs(@lens) select @lenp=@lenp+@lens,@lens=@lenp-@lens,@lenp=@lenp-@lens
if abs(@lenp)>38 set @type=' varchar('+ltrim(case when @lenp>0 then @lenp else abs(@lenp)+1 end)+')'
else if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -9223372036854775808 and 9223372036854775807) set @type=' numeric('+ltrim(abs(@lenp))+',0)'
else if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -2147483648 and 2147483647) set @type=' bigint'
else set @type=' int'
end
else set @type=' datetime'
end
else if not exists (select 1 from @temp where temp is not null and isdate(temp)=0) set @type=' datetime'
else select @type=' varchar('+ltrim(max(datalength(temp)))+')' from @temp where temp is not null
if charindex(':',@col)>0 set @col=stuff(@col,charindex(':',@col),1,@type+case when @cid=@max then '' else ',' end)
else set @col=isnull(@col+',','')+'c'+ltrim(@cid)+@type
update a set a.code=isnull(a.code+',','select ')+case when substring(@type,2,3) in ('flo','num','big','int') then isnull(b.temp,'null') else isnull(quotename(b.temp,char(39)),'null') end from @code a inner join @temp b on a.id=b.id
delete from @temp
set @cid=@cid+1
end
if @max>@cid-1
begin
select @col=stuff(@col,len(@col),1,' sql_variant'),@col=replace(@col,':',' sql_variant,')
update @code set code=code+replicate(',null',@max-@cid+1)
end
set @sql=null
select @sql=isnull(@sql+' union all'+char(13)+char(10),'')+code from @code order by id
print '--> 测试数据: '+@tab
print case left(@tab,1) when '@' then 'declare '+@tab+' table' when '#' then 'if object_id(''tempdb.dbo.'+@tab+''') is not null drop table '+@tab+char(13)+char(10)+'create table '+@tab else 'if object_id('''+@tab+''') is not null drop table '+@tab+char(13)+char(10)+'create table '+@tab end+' ('+@col+')'
print 'insert into '+@tab
print @sql
delete from @data
delete from @code
end
set @sql=null
select @sql=isnull(@sql+char(13)+char(10),char(13)+char(10))+'select * from '+name from @tabs order by id
print @sql
set nocount off
go