SQL用户数据自动生成测试用例（第二版）

Version:V1.002 

Date:2008-05-16 

修改描述：

1、 处理空格带来的异常
2、 增加了形如yyyy-mm-dd hh:mm:ss
                             yyyy-m-d h:m:s     格式的处理

 

set  nocount  on
go
-- 处理当前串中的空格，对时分秒的处理
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

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 ))
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))  from  f_splitSTR( @c , '   ' )
insert   into  #tmp3  select   ltrim ( rtrim (col))  from  f_splitSTR( @s , '
' )

-- 取每一列的最大值，尽量让类型准确
-- update a
-- set col= c.col
-- from #tmp2 a left join (
-- select a.px, max(dbo.F_split(b.col,a.px,' ')) as col
-- from #tmp2 a,#tmp3 b
-- group by a.px) c on a.px = c.px


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

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



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
             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  +   '  varchar( ' + ltrim ( len ( @col )) + ' ), ' ,
                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
END

CLOSE  f
DEALLOCATE  f

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

-- select * from #tmp3

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:2008-05-15   初稿
-- Version:V1.002  Date:2008-05-16  1、 处理空格带来的异常
--                                  2、 增加了形如yyyy-mm-dd hh:mm:ss
--                                               yyyy-m-d h:m:s     格式的处理
*/ ' + 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
go


declare   @s   varchar ( 8000 )
set   @s   =   ' Ta
ID Name dt
 1     aa    2001-01-01 00:01:02
 2        bb   2001-1-1 00:01:02
 3     cc  2001-01-01 0:01:02
 4     dd  2001-1-1 0:1:2
'
exec  sp_autoscript  @s
drop   function  f_splitSTR,f_ischar,f_castdt,F_split
drop   proc  sp_autoscript

set  nocount  off

 

运行结果

 

 

/**/ /*
-- Author:Flystone 
-- Version:V1.001  Date:2008-05-15   初稿
-- Version:V1.002  Date:2008-05-16  1、 处理空格带来的异常
--                                  2、 增加了形如yyyy-mm-dd hh:mm:ss
--                                               yyyy-m-d h:m:s     格式的处理
*/

--  Test Data: Ta
If   object_id ( ' Ta ' )  is   not   null  
     Drop   table  Ta
Go
Create   table  Ta(ID  int ,Name  varchar ( 2 ),dt  datetime )
Go
Insert   into  Ta
select   1 , ' aa ' , ' 2001-01-01 00:01:02 '   union   all
select   2 , ' bb ' , ' 2001-1-1 00:01:02 '   union   all
select   3 , ' cc ' , ' 2001-01-01 0:01:02 '   union   all
select   4 , ' dd ' , ' 2001-1-1 0:1:2 '  
Go
-- Start
Select   *   from  Ta


-- Result:
/**/ /*
ID          Name dt                                                     
----------- ---- ------------------------------------------------------ 
1           aa   2001-01-01 00:01:02.000
2           bb   2001-01-01 00:01:02.000
3           cc   2001-01-01 00:01:02.000
4           dd   2001-01-01 00:01:02.000

（所影响的行数为 4 行）

*/
-- End