测试用例生成第三版

最新推荐文章于 2025-06-03 18:08:35 发布
最新推荐文章于 2025-06-03 18:08:35 发布 · 456 阅读
文章标签：
--处理当前串中的空格，对时分秒的处理
create function f_castdt(@s varchar(8000))
returns varchar(1000)
as
begin
	set @s = ltrim(@s)
	WHILE CHARINDEX('  ',@s)>0
	BEGIN
		SET @s=replace(@s,'  ',' ')
	END
    set @s= case when patindex('%-[0-9][0-9] [0-9][0-9]:%',@s)> 0 then stuff(@s,patindex('%-[0-9][0-9] [0-9][0-9]:%',@s)+3,1,'*')
            when patindex('%-[0-9][0-9] [0-9]:%',@s)> 0 then stuff(@s,patindex('%-[0-9][0-9] [0-9]:%',@s)+3,1,'*')
            when patindex('%-[0-9] [0-9][0-9]:%',@s)> 0 then stuff(@s,patindex('%-[0-9] [0-9][0-9]:%',@s)+2,1,'*')
            when patindex('%-[0-9] [0-9]:%',@s) > 0 then stuff(@s,patindex('%-[0-9] [0-9]:%',@s)+2,1,'*') else @s end
    return @s
end
go
--加一个分隔函数:
create  function F_split(
                @s varchar(8000),          --包含多个数据项的字符串
                @pos int,                 --要获取的数据项的位置
                @split varchar(10)        --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
    IF @s IS NULL RETURN(NULL)
    DECLARE @splitlen int                --分隔符长度
    SELECT @splitlen=LEN(@split+'a')-2
    WHILE @pos>1 AND charindex(@split,@s+@split)>0
        SELECT @pos=@pos-1,
            @s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')
    RETURN replace((nullif(left(@s,charindex(@split,@s+@split)-1),'')),char(13),'')
END
GO

--判断当前串是有字母如果有那么当作varchar
create function f_ischar(@s varchar(100))
returns bit
as
begin
   
	if patindex('%[a-zA-Z]%',@s)>0 
		return 1
	return 0
end 
go

-- 循环截取法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(20)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
	DECLARE @splitlen int
	set @s = ltrim(rtrim(@s))
	SET @splitlen=LEN(@split+'a')-2
	WHILE CHARINDEX(@split,@s)>0
	BEGIN
		INSERT @re VALUES( rtrim(ltrim(LEFT(@s,CHARINDEX(@split,@s)-1))) )
		SET @s=ltrim(STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,''))
	END
	INSERT @re VALUES(@s)
	RETURN
END
GO


create proc sp_autoscript
@s varchar(8000)
as
set nocount on
declare @a varchar(8000),@b varchar(8000),@c varchar(8000)
set @a =  rtrim(left(@s,charindex(char(13),@s) - 1))
set @s =  right(@s,len(@s) - charindex(char(13),@s)- 1)
set @b =  rtrim(left(@s,charindex(char(13),@s) - 1))
set @s =  right(@s,len(@s) - charindex(char(13),@s)- 1)
set @c =  rtrim(left(@s,charindex(char(13),@s) - 1))
set @s =  left(@s,len(@s) -1)

create table #tmp1(px int identity(1,1),col varchar(120),col2 varchar(50),col3 varchar(50))
create table #tmp2(px int identity(1,1),col varchar(120),collen int)
create table #tmp3(px int identity(1,1),col varchar(1000))
insert into #tmp1(col) select ltrim(rtrim(col)) from f_splitSTR(@b,' ')
set @c = dbo.f_castdt(@c)
insert into #tmp2 select ltrim(rtrim(col)),0 from f_splitSTR(@c,' ')
insert into #tmp3 select ltrim(rtrim(col)) from f_splitSTR(@s,'
')

--取每一列的最大值，尽量让类型准确


update a
set collen= (select top 1 len( dbo.F_split(dbo.f_castdt(b.col+' '),c.px,' ')) as tt
from #tmp2 c,#tmp3 b where c.px = a.px
group by c.px,b.col
order by  tt desc
 )
from #tmp2 a 

--select * from #tmp3
--select * from #tmp2
--select top 1 dbo.F_split(dbo.f_castdt(b.col+' '),a.px,' ') as tt
--from #tmp2 a,#tmp3 b
--group by a.px,b.col
--order by  1

declare @px int,@col varchar(100),@maxid int,@colen int
select @maxid = count(1) from #tmp1

DECLARE f CURSOR FOR SELECT px,col,collen FROM #tmp2
OPEN f
FETCH NEXT FROM f INTO @px,@col,@colen



WHILE @@FETCH_STATUS = 0
BEGIN

    set @col=  --还原datatime数据用于判断 
       case when patindex('%-[0-9][0-9]*[0-9][0-9]:%',@col)> 0 then stuff(@col,patindex('%-[0-9][0-9]*[0-9][0-9]:%',@col)+3,1,' ')
            when patindex('%-[0-9][0-9]*[0-9]:%',@col)> 0 then stuff(@col,patindex('%-[0-9][0-9]*[0-9]:%',@col)+3,1,' ')
            when patindex('%-[0-9]*[0-9][0-9]:%',@col)> 0 then stuff(@col,patindex('%-[0-9]*[0-9][0-9]:%',@col)+2,1,' ')
            when patindex('%-[0-9]*[0-9]:%',@col) > 0 then stuff(@col,patindex('%-[0-9]*[0-9]:%',@col)+2,1,' ') else @col end

	if ISDATE(@col) = 1 
		if len(@col) <= 10
	    	update #tmp1
			set col = col + ' smalldatetime,',
                col2 = case when px = 1 then ' select ''''' else '''''' end,
				col3 = case when px = @maxid then ''''' union all'  else ''''',' end
	        where px = @px
		else
            update #tmp1
			set col = col + ' datetime,',
                col2 = case when px = 1 then ' select ''''' else '''''' end,
				col3 = case when px = @maxid then ''''' union all' else ''''',' end
	        where px = @px
	else
		if isnumeric(@col) = 1 and left(@col,1) <> '0'
            if charindex('.',@col) > 0 
				update #tmp1
				set col = col + ' numeric('+ltrim(len(@col) -1 )+','+ ltrim(len(@col) - charindex('.',@col))+'),',
                col2 = case when px = 1 then ' select ' else '' end,
				col3 = case when px = @maxid then ' union all' else ',' end
                where px = @px
            else
				update #tmp1
				set col = col + ' int,',
				col2 = case when px = 1 then 'select ' else '' end,
				col3 = case when px = @maxid then ' union all' else ',' end
                where px = @px
		else
		--	if dbo.f_ischar(@col) = 1 
				update #tmp1
				set col = col + ' nvarchar('+ltrim((@colen))+'),',
				col2 = case when px = 1 then ' select ''''' else '''''' end,
				col3 = case when px = @maxid then ''''' union all' else ''''',' end    
                where px = @px

		
	FETCH NEXT FROM f INTO @px,@col,@colen
END

CLOSE f
DEALLOCATE f

update #tmp3
set col = dbo.f_castdt(col)

--select * from #tmp1

declare @sql varchar(8000)
declare @sql1 varchar(8000)
select @sql = isnull(@sql+'+' ,'')+''''
+col2 + '''+dbo.F_split(col,'+ltrim(px)+','' '')' + '+'''+col3+'''' 
from #tmp1
select @sql1 = isnull(@sql1,'') + col 
from #tmp1
--select @sql

exec('update #tmp3 set col = '+@sql)
--还原datetime数据
update #tmp3
set col = 
   case when patindex('%-[0-9][0-9]*[0-9][0-9]:%',col)> 0 then stuff(col,patindex('%-[0-9][0-9]*[0-9][0-9]:%',col)+3,1,' ')
        when patindex('%-[0-9][0-9]*[0-9]:%',col)> 0 then stuff(col,patindex('%-[0-9][0-9]*[0-9]:%',col)+3,1,' ')
        when patindex('%-[0-9]*[0-9][0-9]:%',col)> 0 then stuff(col,patindex('%-[0-9]*[0-9][0-9]:%',col)+2,1,' ')
        when patindex('%-[0-9]*[0-9]:%',col) > 0 then stuff(col,patindex('%-[0-9]*[0-9]:%',col)+2,1,' ') else col end


set @sql = ''
set @sql ='------------------------------------
-- Author:Flystone 
-- Version:V1.001  
-- Date:'+convert(char(19),getdate(),120)+'
------------------------------------'+char(10)+
'
-- Test Data: '+@a+ char(10)+
'If object_id('''+@a+''') is not null '+char(13)+
'    Drop table '+@a + char(10)+
'Go'+char(13)+

'Create table '+@a+'('+left(@sql1,len(@sql1) - 1)+')'+char(10)+
'Go'
+char(10)+
'Insert into '+ @a+'
'
select @sql = @sql+ col +char(13) from #tmp3
set @sql = left(@sql,len(@sql) - 10)
set @sql =  @sql + char(13) +
    'Go'+char(10)+
    '--Start'+char(10)+
'Select * from '+@a+char(10)+'

--Result:
/*


*/
--End '
print @sql
drop table #tmp1,#tmp2,#tmp3
set nocount off
go


declare @s varchar(8000)
set @s = 'ta
id    mname 
1    ab13 
2    abc 
3    sime 
4    123  
5    124ab 
6    中华 
7    2009-02-02 
8    12 
9    abc 
10    a%-bc 
11    a_bc 
'
exec sp_autoscript @s



--drop function f_castdt,f_split,f_ischar,f_splitstr
--drop proc sp_autoscript