怎么比较两段存储过程的异同？

[jcdwin7]

第一个是
CREATE PROCEDURE [dbo].prGetDocNoCom
@TableName nvarchar(30), @colName nvarchar(50) = NULL, @StartDate nvarchar(10), @OtherKey nvarchar(4000), @docLen int = NULL, @docNo nvarchar(100) OUTPUT
AS
DECLARE @KeyName nvarchar(50), @Length int
DECLARE @StartValue nvarchar(100)
DECLARE @PrefixOrder nvarchar(6) , @IsAlpha nvarchar(1), @ViewRef nvarchar(30), @i integer,@TempDocNo nvarchar(20),@TempPrefix nvarchar(20), @ReGen nvarchar(10)
DECLARE @TempYear nvarchar(3), @TempRightYear nvarchar(1), @TempLeftYearInt int, @TempYearCharShift int
DECLARE @TempDayInt int, @TempDayCharShift int
DECLARE @StartDateValue datetime
DECLARE @RecCnt int

DECLARE @WaterNo nvarchar(100), @WaterNoDigit integer, @TempWaterNoDigit integer, @WaterNoChr nvarchar(100), @TempWaterCharShift int, @WaterNoCnt int
DECLARE @sqlWhereY nvarchar(4000), @sqlWhereM nvarchar(4000), @sqlWhereD nvarchar(4000), @sqlCommon nvarchar(4000), @sqlUpdateStart nvarchar(4000), @sqlSelectStart nvarchar(4000), @sqlSelectTable nvarchar(4000)

SET @sqlWhereY = ''
SET @sqlWhereM = ''
SET @sqlWhereD = ''

IF @StartDate = ''
SET @StartDateValue = GetDate()
ELSE
SET @StartDateValue = CAST(@StartDate AS datetime)

SET @ReGen=''
SELECT @PrefixOrder = PrefixOrder, @IsAlpha = CAST(IsAlpha AS nvarchar(1)), @ViewRef = IsNull(ViewRef, TableName) FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')

IF @PrefixOrder IS NOT NULL
BEGIN
SET @i = 1
SET @TempDocNo = ''
WHILE @i < Len(@PrefixOrder) + 1
BEGIN
IF SubString(@PrefixOrder,@i,1) = '1'
SELECT @TempDocNo=@TempDocNo + Prefix1 FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')
IF SubString(@PrefixOrder,@i,1) = '2'
SELECT @TempDocNo=@TempDocNo + Prefix2 FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')
IF SubString(@PrefixOrder,@i,1) = '3'
SELECT @TempDocNo=@TempDocNo + Prefix3 FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')
IF SubString(@PrefixOrder,@i,1) = 'y'
BEGIN
SELECT @TempPrefix = PrefixY FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')
IF Lower(@TempPrefix) = 'yy'
BEGIN
SET @TempYear = Right('000'+LTrim(Str(Year(@StartDateValue) - 1900)),3)
SET @TempRightYear = Right(RTRIM(@TempYear),1)
SET @TempLeftYearInt = CAST(LEFT(LTrim(@TempYear),2) AS int) + 55
IF @TempLeftYearInt-55 >= 10
BEGIN
SET @TempYearCharShift = 0
IF (@TempLeftYearInt+@TempYearCharShift >= ASCII('I'))
SET @TempYearCharShift = @TempYearCharShift + 1
IF (@TempLeftYearInt+@TempYearCharShift >= ASCII('O'))
SET @TempYearCharShift = @TempYearCharShift + 1
IF (@TempLeftYearInt+@TempYearCharShift >= ASCII('U'))
SET @TempYearCharShift = @TempYearCharShift + 1
IF (@TempLeftYearInt+@TempYearCharShift >= ASCII('V'))
SET @TempYearCharShift = @TempYearCharShift + 1
SET @TempDocNo=@TempDocNo + Char(@TempLeftYearInt+@TempYearCharShift)
END
ELSE
SET @TempDocNo=@TempDocNo + Right(N'0'+LTrim(Str(@TempLeftYearInt-55)),1)


SET @TempDocNo=@TempDocNo + @TempRightYear
END
ELSE IF Lower(@TempPrefix) = 'yyyy'
SET @TempDocNo=@TempDocNo + Right(LTrim(Str(Year(@StartDateValue))),4)
SET @ReGen = 'year'
SET @sqlWhereY = ' AND Year(StartDate) = Year(@StartDateValue) '
END
IF SubString(@PrefixOrder,@i,1) = 'm'
BEGIN
SELECT @TempPrefix = PrefixM FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')

IF Lower(@TempPrefix) = 'm'
BEGIN
IF Month(@StartDateValue) >= 10
SET @TempDocNo=@TempDocNo + Char(Month(@StartDateValue)+55)
ELSE
SET @TempDocNo=@TempDocNo + Right('0'+LTrim(Str(Month(@StartDateValue))),1)
END
ELSE IF Lower(@TempPrefix) = 'mm'
SET @TempDocNo=@TempDocNo + Right('0'+LTrim(Str(Month(@StartDateValue))),2)
SET @ReGen = 'month'
SET @sqlWhereM = ' AND Month(StartDate) = Month(@StartDateValue) '
END
IF SubString(@PrefixOrder,@i,1) = 'd'
BEGIN
SELECT @TempPrefix = PrefixD FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')
IF Lower(@TempPrefix) = 'd'
BEGIN
SET @TempDayInt = Day(@StartDateValue) + 55
IF @TempDayInt-55 >= 10
BEGIN
SET @TempDayCharShift = 0
IF (@TempDayInt+@TempDayCharShift >= ASCII('I'))
SET @TempDayCharShift = @TempDayCharShift + 1
IF (@TempDayInt+@TempDayCharShift >= ASCII('O'))
SET @TempDayCharShift = @TempDayCharShift + 1
IF (@TempDayInt+@TempDayCharShift >= ASCII('U'))
SET @TempDayCharShift = @TempDayCharShift + 1
IF (@TempDayInt+@TempDayCharShift >= ASCII('V'))
SET @TempDayCharShift = @TempDayCharShift + 1
SET @TempDocNo=@TempDocNo + Char(@TempDayInt+@TempDayCharShift)
END
ELSE
SET @TempDocNo=@TempDocNo + Right(N'0'+LTrim(Str(@TempDayInt-55)),1)
END
ELSE IF Lower(@TempPrefix) = 'dd'
SET @TempDocNo=@TempDocNo + Right('0'+LTrim(Str(Day(@StartDateValue))),2)
SET @ReGen = 'day'
SET @sqlWhereD = ' AND Day(StartDate) = Day(@StartDateValue) '
END
SET @i = @i + 1
END
END
ELSE
BEGIN
SET @ViewRef = @TableName
SET @TempDocNo = ''
END

-- Get the column length if not provided
IF @docLen IS NULL AND @colName IS NOT NULL
BEGIN
SELECT @docLen = a.prec FROM syscolumns a INNER JOIN sysobjects b ON a.id = b.id WHERE b.name = @ViewRef AND a.name = @colName
END

/* Get the name of the key (or the running code 流水號) and the length of the running code */
DECLARE @kColName nvarchar(100), @kColPrec int , @kColDesc nvarchar(4000), @kColValue nvarchar(4000), @sqlOtherKey nvarchar(4000), @fnXmlParserGetValueByName nvarchar(500)
SET @fnXmlParserGetValueByName = dbo.fnBaseDbName() + '.dbo.fnXmlParserGetValueByName'
SET @sqlOtherKey =''

[jcdwin7]

BEGIN
DECLARE FindKeyCursor CURSOR STATIC FORWARD_ONLY
FOR (SELECT a.name, a.prec, CAST(d.value AS nvarchar(4000)) FROM (
syscolumns a INNER JOIN sysobjects b ON a.id = b.id
INNER JOIN sysindexkeys c ON a.colid = c.colid AND b.id = c.id AND c.indid = 1
LEFT OUTER JOIN ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'TABLE', @ViewRef, 'column', DEFAULT) d ON a.name = d.objname
) WHERE b.type = 'U' AND b.name = @ViewRef
UNION SELECT a.name, a.prec, CAST(d.value AS nvarchar(4000)) FROM (
syscolumns a INNER JOIN sysobjects b ON a.id = b.id
LEFT OUTER JOIN ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'VIEW', @ViewRef, 'column', DEFAULT) d ON a.name = d.objname
) WHERE b.type = 'V' AND b.name = @ViewRef)

OPEN FindKeyCursor
FETCH NEXT FROM FindKeyCursor INTO @kColName, @kColPrec , @kColDesc

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @kColName
IF CHARINDEX('<docno>',@kColDesc) > 0 OR @@CURSOR_ROWS = 1 -- has <docno> or no of row is exactly one
BEGIN
SET @KeyName = @kColName
SET @Length = @kColPrec
END
ELSE -- does not have <docno>, so it is OtherKey
BEGIN

EXEC @kColValue = @fnXmlParserGetValueByName @OtherKey, @kColName
SET @sqlOtherKey = @sqlOtherKey + ' AND ' +@kColName +' = '''+ IsNull(@kColValue, '') + ''''
END
FETCH NEXT FROM FindKeyCursor INTO @kColName, @kColPrec , @kColDesc
END
CLOSE FindKeyCursor
DEALLOCATE FindKeyCursor
END

SET @KeyName = IsNull(@colName, @KeyName)
SET @Length = IsNull(@docLen, @Length)

-- Insert Record If not find
SET @sqlCommon = N'SELECT @RecCnt = Count(*) FROM CommonStart WHERE Upper(TableName) = Upper(@TableName) AND Upper(IsNull(ColName, '''')) = Upper(IsNull(@ColName, '''')) AND Upper(OtherKey) = Upper(@OtherKey) ' + @sqlWhereY + @sqlWhereM + @sqlWhereD
EXEC sp_executesql @sqlCommon, N'@TableName nvarchar(30), @ColName nvarchar(50), @StartDateValue datetime, @RecCnt int OUTPUT, @OtherKey nvarchar(4000)', @TableName = @TableName, @ColName = @ColName, @OtherKey = @OtherKey, @StartDateValue = @StartDateValu
e, @RecCnt = @RecCnt OUTPUT
IF @RecCnt = 0 -- Not find
BEGIN
INSERT INTO CommonStart (TableName , ColName, StartDate , StartValue, OtherKey) Values (@TableName, @ColName, @StartDateValue,1, @OtherKey)
END

IF @@ERROR<>0 RAISERROR ('Initial Doc No # not set. Please set the Doc No ', 16, 1)

--Increment StartValue
BEGIN
SET @sqlSelectStart = N'SELECT @StartValue=Right(Replicate(N''0'',@Length)+Cast(StartValue AS nvarchar),@Length) FROM CommonStart WHERE Upper(TableName)=Upper(@TableName) AND Upper(IsNull(ColName, '''')) = Upper(IsNull(@ColName, '''')) AND Upper(OtherKey
) = Upper(@OtherKey) ' + @sqlWhereY + @sqlWhereM + @sqlWhereD
SET @sqlUpdateStart = N'UPDATE CommonStart SET StartValue = StartValue+1 WHERE Upper(TableName) = Upper(@TableName) AND Upper(IsNull(ColName, '''')) = Upper(IsNull(@ColName, '''')) AND Upper(OtherKey) = Upper(@OtherKey) ' + @sqlWhereY + @sqlWhereM + @sql
WhereD
SET @sqlSelectTable = N'SELECT @RecCnt = Count(*) FROM '+@ViewRef+' WHERE '+ @KeyName +' = @StartValue ' + @sqlOtherKey

EXEC sp_executesql @sqlSelectTable, N'@StartValue nvarchar(100), @RecCnt int OUTPUT', @StartValue = @StartValue, @RecCnt = @RecCnt OUTPUT

WHILE @RecCnt > 0 OR @StartValue IS NULL
BEGIN
EXEC sp_executesql @sqlSelectStart, N'@TableName nvarchar(30), @ColName nvarchar(50), @StartDateValue datetime, @Length int, @StartValue nvarchar(100) OUTPUT, @OtherKey nvarchar(4000)', @TableName = @TableName, @ColName = @ColName, @StartDateValue = @St
artDateValue, @Length = @Length, @StartValue = @StartValue OUTPUT, @OtherKey = @OtherKey

IF @IsAlpha = '1'
BEGIN
SET @WaterNo = @StartValue
SET @WaterNoChr = N''
SET @WaterNoCnt = 0

WHILE @WaterNoCnt < @Length - Len(@TempDocNo)
BEGIN
SET @WaterNoDigit = (CAST(@WaterNo AS int) / POWER(32,@WaterNoCnt)) % 32
IF @WaterNoDigit < 10
SET @WaterNoChr = Right(N'0'+LTrim(Str(@WaterNoDigit)),1) + @WaterNoChr
ELSE
BEGIN
SET @TempWaterNoDigit = @WaterNoDigit+ 55
SET @TempWaterCharShift = 0

IF (@TempWaterNoDigit+@TempWaterCharShift >= ASCII('I'))
SET @TempWaterCharShift = @TempWaterCharShift + 1
IF (@TempWaterNoDigit+@TempWaterCharShift >= ASCII('O'))
SET @TempWaterCharShift = @TempWaterCharShift + 1
IF (@TempWaterNoDigit+@TempWaterCharShift >= ASCII('U'))
SET @TempWaterCharShift = @TempWaterCharShift + 1
IF (@TempWaterNoDigit+@TempWaterCharShift >= ASCII('V'))
SET @TempWaterCharShift = @TempWaterCharShift + 1
SET @WaterNoChr = Char(@TempWaterNoDigit+@TempWaterCharShift) + @WaterNoChr
END

SET @WaterNoCnt = @WaterNoCnt + 1
END

SET @WaterNo = @WaterNoChr
SET @StartValue = @TempDocNo + @WaterNo
END
ELSE
SET @StartValue = @TempDocNo + Right(@StartValue, @Length - Len(@TempDocNo))

EXEC sp_executesql @sqlUpdateStart, N'@TableName nvarchar(30), @ColName nvarchar(50), @StartDateValue datetime, @OtherKey nvarchar(4000)', @TableName = @TableName, @ColName = @ColName, @StartDateValue = @StartDateValue, @OtherKey = @OtherKey

EXEC sp_executesql @sqlSelectTable, N'@StartValue nvarchar(100), @RecCnt int OUTPUT', @StartValue = @StartValue, @RecCnt = @RecCnt OUTPUT
END
END

IF @StartValue IS NULL RAISERROR('Expected Doc No not gained !',16,1)
ELSE SELECT @docNo = @StartValue
RETURN

[jcdwin7]

第二个
CREATE PROCEDURE [dbo].prGetDocNoCom
@TableName nvarchar(30), @colName nvarchar(50) = NULL, @StartDate nvarchar(10), @OtherKey nvarchar(4000), @docLen int = NULL, @docNo nvarchar(100) OUTPUT
AS
DECLARE @KeyName nvarchar(50), @Length int
DECLARE @StartValue nvarchar(100)
DECLARE @PrefixOrder nvarchar(6) , @IsAlpha nvarchar(1), @ViewRef nvarchar(30), @i integer,@TempDocNo nvarchar(20),@TempPrefix nvarchar(20), @ReGen nvarchar(10)
DECLARE @TempYear nvarchar(3), @TempRightYear nvarchar(1), @TempLeftYearInt int, @TempYearCharShift int
DECLARE @TempDayInt int, @TempDayCharShift int
DECLARE @StartDateValue datetime
DECLARE @RecCnt int

DECLARE @WaterNo nvarchar(100), @WaterNoDigit integer, @TempWaterNoDigit integer, @WaterNoChr nvarchar(100), @TempWaterCharShift int, @WaterNoCnt int
DECLARE @sqlWhereY nvarchar(4000), @sqlWhereM nvarchar(4000), @sqlWhereD nvarchar(4000), @sqlCommon nvarchar(4000), @sqlUpdateStart nvarchar(4000), @sqlSelectStart nvarchar(4000), @sqlSelectTable nvarchar(4000)

SET @sqlWhereY = ''
SET @sqlWhereM = ''
SET @sqlWhereD = ''

IF @StartDate = ''
SET @StartDateValue = GetDate()
ELSE
SET @StartDateValue = CAST(@StartDate AS datetime)

SET @ReGen=''
SELECT @PrefixOrder = PrefixOrder, @IsAlpha = CAST(IsAlpha AS nvarchar(1)), @ViewRef = IsNull(ViewRef, TableName) FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')

IF @PrefixOrder IS NOT NULL
BEGIN
SET @i = 1
SET @TempDocNo = ''
WHILE @i < Len(@PrefixOrder) + 1
BEGIN
IF SubString(@PrefixOrder,@i,1) = '1'
SELECT @TempDocNo=@TempDocNo + Prefix1 FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')
IF SubString(@PrefixOrder,@i,1) = '2'
SELECT @TempDocNo=@TempDocNo + Prefix2 FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')
IF SubString(@PrefixOrder,@i,1) = '3'
SELECT @TempDocNo=@TempDocNo + Prefix3 FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')
IF SubString(@PrefixOrder,@i,1) = 'y'
BEGIN
SELECT @TempPrefix = PrefixY FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')
IF Lower(@TempPrefix) = 'yy'
BEGIN
SET @TempYear = Right('000'+LTrim(Str(Year(@StartDateValue) - 1900)),3)
SET @TempRightYear = Right(RTRIM(@TempYear),1)
SET @TempLeftYearInt = CAST(LEFT(LTrim(@TempYear),2) AS int) + 55
IF @TempLeftYearInt-55 >= 10
BEGIN
SET @TempYearCharShift = 0
IF (@TempLeftYearInt+@TempYearCharShift >= ASCII('I'))
SET @TempYearCharShift = @TempYearCharShift + 1
IF (@TempLeftYearInt+@TempYearCharShift >= ASCII('O'))
SET @TempYearCharShift = @TempYearCharShift + 1
IF (@TempLeftYearInt+@TempYearCharShift >= ASCII('U'))
SET @TempYearCharShift = @TempYearCharShift + 1
IF (@TempLeftYearInt+@TempYearCharShift >= ASCII('V'))
SET @TempYearCharShift = @TempYearCharShift + 1
SET @TempDocNo=@TempDocNo + Char(@TempLeftYearInt+@TempYearCharShift)
END
ELSE
SET @TempDocNo=@TempDocNo + Right(N'0'+LTrim(Str(@TempLeftYearInt-55)),1)


SET @TempDocNo=@TempDocNo + @TempRightYear
END
ELSE IF Lower(@TempPrefix) = 'yyyy'
SET @TempDocNo=@TempDocNo + Right(LTrim(Str(Year(@StartDateValue))),4)
SET @ReGen = 'year'
SET @sqlWhereY = ' AND Year(StartDate) = Year(@StartDateValue) '
END
IF SubString(@PrefixOrder,@i,1) = 'm'
BEGIN
SELECT @TempPrefix = PrefixM FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')

IF Lower(@TempPrefix) = 'm'
BEGIN
IF Month(@StartDateValue) >= 10
SET @TempDocNo=@TempDocNo + Char(Month(@StartDateValue)+55)
ELSE
SET @TempDocNo=@TempDocNo + Right('0'+LTrim(Str(Month(@StartDateValue))),1)
END
ELSE IF Lower(@TempPrefix) = 'mm'
SET @TempDocNo=@TempDocNo + Right('0'+LTrim(Str(Month(@StartDateValue))),2)
SET @ReGen = 'month'
SET @sqlWhereM = ' AND Month(StartDate) = Month(@StartDateValue) '
END
IF SubString(@PrefixOrder,@i,1) = 'd'
BEGIN
SELECT @TempPrefix = PrefixD FROM CommonStartPrefix WHERE TableName = @TableName AND IsNull(ColName, '') = IsNull(@ColName, '')
IF Lower(@TempPrefix) = 'd'
BEGIN
SET @TempDayInt = Day(@StartDateValue) + 55
IF @TempDayInt-55 >= 10
BEGIN
SET @TempDayCharShift = 0
IF (@TempDayInt+@TempDayCharShift >= ASCII('I'))
SET @TempDayCharShift = @TempDayCharShift + 1
IF (@TempDayInt+@TempDayCharShift >= ASCII('O'))
SET @TempDayCharShift = @TempDayCharShift + 1
IF (@TempDayInt+@TempDayCharShift >= ASCII('U'))
SET @TempDayCharShift = @TempDayCharShift + 1
IF (@TempDayInt+@TempDayCharShift >= ASCII('V'))
SET @TempDayCharShift = @TempDayCharShift + 1
SET @TempDocNo=@TempDocNo + Char(@TempDayInt+@TempDayCharShift)
END
ELSE
SET @TempDocNo=@TempDocNo + Right(N'0'+LTrim(Str(@TempDayInt-55)),1)
END
ELSE IF Lower(@TempPrefix) = 'dd'
SET @TempDocNo=@TempDocNo + Right('0'+LTrim(Str(Day(@StartDateValue))),2)
SET @ReGen = 'day'
SET @sqlWhereD = ' AND Day(StartDate) = Day(@StartDateValue) '
END
SET @i = @i + 1
END
END
ELSE
BEGIN
SET @ViewRef = @TableName
SET @TempDocNo = ''
END

-- Get the column length if not provided
IF @docLen IS NULL AND @colName IS NOT NULL
BEGIN
SELECT @docLen = a.prec FROM syscolumns a INNER JOIN sysobjects b ON a.id = b.id WHERE b.name = @ViewRef AND a.name = @colName
END

[jcdwin7]

/* Get the name of the key (or the running code 流水號) and the length of the running code */
DECLARE @kColName nvarchar(100), @kColPrec int , @kColDesc nvarchar(4000), @kColValue nvarchar(4000), @sqlOtherKey nvarchar(4000), @fnXmlParserGetValueByName nvarchar(500)
SET @fnXmlParserGetValueByName = dbo.fnBaseDbName() + '.dbo.fnXmlParserGetValueByName'
SET @sqlOtherKey =''
BEGIN
DECLARE FindKeyCursor CURSOR STATIC FORWARD_ONLY
FOR (SELECT a.name, a.prec, CAST(d.value AS nvarchar(4000)) FROM (
syscolumns a INNER JOIN sysobjects b ON a.id = b.id
INNER JOIN sysindexkeys c ON a.colid = c.colid AND b.id = c.id AND c.indid = 1
LEFT OUTER JOIN ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'TABLE', @ViewRef, 'column', DEFAULT) d ON a.name = d.objname COLLATE Chinese_Taiwan_Stroke_CI_AS
) WHERE b.type = 'U' AND b.name = @ViewRef
UNION SELECT a.name, a.prec, CAST(d.value AS nvarchar(4000)) FROM (
syscolumns a INNER JOIN sysobjects b ON a.id = b.id
LEFT OUTER JOIN ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'VIEW', @ViewRef, 'column', DEFAULT) d ON a.name = d.objname COLLATE Chinese_Taiwan_Stroke_CI_AS
) WHERE b.type = 'V' AND b.name = @ViewRef)

OPEN FindKeyCursor
FETCH NEXT FROM FindKeyCursor INTO @kColName, @kColPrec , @kColDesc

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @kColName
IF CHARINDEX('<docno>',@kColDesc) > 0 OR @@CURSOR_ROWS = 1 -- has <docno> or no of row is exactly one
BEGIN
SET @KeyName = @kColName
SET @Length = @kColPrec
END
ELSE -- does not have <docno>, so it is OtherKey
BEGIN

EXEC @kColValue = @fnXmlParserGetValueByName @OtherKey, @kColName
SET @sqlOtherKey = @sqlOtherKey + ' AND ' +@kColName +' = '''+ IsNull(@kColValue, '') + ''''
END
FETCH NEXT FROM FindKeyCursor INTO @kColName, @kColPrec , @kColDesc
END
CLOSE FindKeyCursor
DEALLOCATE FindKeyCursor
END

SET @KeyName = IsNull(@colName, @KeyName)
SET @Length = IsNull(@docLen, @Length)

-- Insert Record If not find
SET @sqlCommon = N'SELECT @RecCnt = Count(*) FROM CommonStart WHERE Upper(TableName) = Upper(@TableName) AND Upper(IsNull(ColName, '''')) = Upper(IsNull(@ColName, '''')) AND Upper(OtherKey) = Upper(@OtherKey) ' + @sqlWhereY + @sqlWhereM + @sqlWhereD
EXEC sp_executesql @sqlCommon, N'@TableName nvarchar(30), @ColName nvarchar(50), @StartDateValue datetime, @RecCnt int OUTPUT, @OtherKey nvarchar(4000)', @TableName = @TableName, @ColName = @ColName, @OtherKey = @OtherKey, @StartDateValue = @StartDateValu
e, @RecCnt = @RecCnt OUTPUT
IF @RecCnt = 0 -- Not find
BEGIN
INSERT INTO CommonStart (TableName , ColName, StartDate , StartValue, OtherKey) Values (@TableName, @ColName, @StartDateValue,1, @OtherKey)
END

IF @@ERROR<>0 RAISERROR ('Initial Doc No # not set. Please set the Doc No ', 16, 1)

--Increment StartValue
BEGIN
SET @sqlSelectStart = N'SELECT @StartValue=Right(Replicate(N''0'',@Length)+Cast(StartValue AS nvarchar),@Length) FROM CommonStart WHERE Upper(TableName)=Upper(@TableName) AND Upper(IsNull(ColName, '''')) = Upper(IsNull(@ColName, '''')) AND Upper(OtherKey
) = Upper(@OtherKey) ' + @sqlWhereY + @sqlWhereM + @sqlWhereD
SET @sqlUpdateStart = N'UPDATE CommonStart SET StartValue = StartValue+1 WHERE Upper(TableName) = Upper(@TableName) AND Upper(IsNull(ColName, '''')) = Upper(IsNull(@ColName, '''')) AND Upper(OtherKey) = Upper(@OtherKey) ' + @sqlWhereY + @sqlWhereM + @sql
WhereD
SET @sqlSelectTable = N'SELECT @RecCnt = Count(*) FROM '+@ViewRef+' WHERE '+ @KeyName +' = @StartValue ' + @sqlOtherKey

EXEC sp_executesql @sqlSelectTable, N'@StartValue nvarchar(100), @RecCnt int OUTPUT', @StartValue = @StartValue, @RecCnt = @RecCnt OUTPUT

WHILE @RecCnt > 0 OR @StartValue IS NULL
BEGIN
EXEC sp_executesql @sqlSelectStart, N'@TableName nvarchar(30), @ColName nvarchar(50), @StartDateValue datetime, @Length int, @StartValue nvarchar(100) OUTPUT, @OtherKey nvarchar(4000)', @TableName = @TableName, @ColName = @ColName, @StartDateValue = @St
artDateValue, @Length = @Length, @StartValue = @StartValue OUTPUT, @OtherKey = @OtherKey

IF @IsAlpha = '1'
BEGIN
SET @WaterNo = @StartValue
SET @WaterNoChr = N''
SET @WaterNoCnt = 0

WHILE @WaterNoCnt < @Length - Len(@TempDocNo)
BEGIN
SET @WaterNoDigit = (CAST(@WaterNo AS int) / POWER(32,@WaterNoCnt)) % 32
IF @WaterNoDigit < 10
SET @WaterNoChr = Right(N'0'+LTrim(Str(@WaterNoDigit)),1) + @WaterNoChr
ELSE
BEGIN
SET @TempWaterNoDigit = @WaterNoDigit+ 55
SET @TempWaterCharShift = 0

IF (@TempWaterNoDigit+@TempWaterCharShift >= ASCII('I'))
SET @TempWaterCharShift = @TempWaterCharShift + 1
IF (@TempWaterNoDigit+@TempWaterCharShift >= ASCII('O'))
SET @TempWaterCharShift = @TempWaterCharShift + 1
IF (@TempWaterNoDigit+@TempWaterCharShift >= ASCII('U'))
SET @TempWaterCharShift = @TempWaterCharShift + 1
IF (@TempWaterNoDigit+@TempWaterCharShift >= ASCII('V'))
SET @TempWaterCharShift = @TempWaterCharShift + 1
SET @WaterNoChr = Char(@TempWaterNoDigit+@TempWaterCharShift) + @WaterNoChr
END

SET @WaterNoCnt = @WaterNoCnt + 1
END

SET @WaterNo = @WaterNoChr
SET @StartValue = @TempDocNo + @WaterNo
END
ELSE
SET @StartValue = @TempDocNo + Right(@StartValue, @Length - Len(@TempDocNo))

EXEC sp_executesql @sqlUpdateStart, N'@TableName nvarchar(30), @ColName nvarchar(50), @StartDateValue datetime, @OtherKey nvarchar(4000)', @TableName = @TableName, @ColName = @ColName, @StartDateValue = @StartDateValue, @OtherKey = @OtherKey

EXEC sp_executesql @sqlSelectTable, N'@StartValue nvarchar(100), @RecCnt int OUTPUT', @StartValue = @StartValue, @RecCnt = @RecCnt OUTPUT
END
END

IF @StartValue IS NULL RAISERROR('Expected Doc No not gained !',16,1)
ELSE SELECT @docNo = @StartValue
RETURN

[jiangchuandong]

EditPlus....

[rivery]

都存储为文本文件。
如果你装了vss，可以把其中一个上传到vss上，然后和本地的另外一个比较，非常直观的。