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 , '
' )

-- 取每一列的最大值，尽量让类型准确
-- updatea
-- setcol=c.col
-- from#tmp2aleftjoin(
-- selecta.px,max(dbo.F_split(b.col,a.px,''))ascol
-- from#tmp2a,#tmp3b
-- groupbya.px)cona.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 ''''' unionall ' else ''''' , ' end
where px = @px
else
update #tmp1
set col = col + ' datetime, ' ,
col2 = case