大家帮我看看如果使用这段存储过程,在事务并发的情况下有没有可能产生重复流水号的可能?
不用考虑生成的规则,就事务而言
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
[解决办法]
设置Set xact_abort on，启用事务，确保事务处理的一致性完整性。
如果逻辑上没有错误，就不应该重复。
[解决办法]
最好在事务中处理。
[解决办法]
楼上学得对， 这样会容易引起死锁，使用时要警慎。
[解决办法]
肯定会出现的,比如网络速度很慢,或者产生死锁时都有可能.
