鷹翔宇空

-- ==================================================
-- 名稱：得到單據(jù)流水號
-- 實(shí)現(xiàn)功能：取得對應(yīng)表的計(jì)數(shù)器，實(shí)現(xiàn)流水號功能．
-- 調(diào)用示例：SELECT F_LT_GetOrderNo(FId) as FID, * from Tab1 T1
??????????????????? left outer join T_OrderList T2 on T1.FTabID = T2.FID
-- ==================================================
CREATE TABLE T_OrderList(
FID int IDENTITY (1, 1) NOT NULL,
FIncCount int -- 計(jì)數(shù)器
)

CREATE FUNCTION F_LT_GetOrderNo(@ID int)
AS RETURN VARCHAR(32)
DECLARE @OrderNo int
SELECT @OrderNo = FIncCount FROM T_OrderList WHERE FID = @ID
??? -- 取得編號后，計(jì)數(shù)器加１
UPDATE T_OrderList SET FIncCount = FIncCount +1 -- 函數(shù)中不允許執(zhí)行UPDATE，這種情況要怎么處理．
RETURNS @OrderNo

-- 系統(tǒng)單據(jù)表，存放系統(tǒng)所以業(yè)務(wù)單據(jù)列表，存有生成流水號計(jì)數(shù)器
CREATE? TABLE? T_OrderList(?
?????????? FID? int? IDENTITY? (1,? 1)? NOT? NULL,?
?????????? FIncCount? int? --? 計(jì)數(shù)器?
?????????? FOrder varchar(30) not Null
?????????? )?

-- 系統(tǒng)業(yè)務(wù)單據(jù)，存放企業(yè)日常業(yè)務(wù)數(shù)據(jù)，具體每單有一個(gè)單據(jù)流水號
CREATE? TABLE? T_Order(?
?????????? FID? int? IDENTITY? (1,? 1)? NOT? NULL,?
?????????? FNumber varchar(40),? -- 單據(jù)流水號?
?????????? FOrderInfo varchar(30)
?????????? )?

-- 現(xiàn)系統(tǒng)要求自動(dòng)運(yùn)算，將運(yùn)算后的數(shù)據(jù)填充到T_Order業(yè)務(wù)表中．填充時(shí)各記錄要生成不同的單據(jù)流水號．我原先的實(shí)現(xiàn)想法是用存儲(chǔ)過程：
CREATE??? PROCEDURE P_OnlyC
? @CodeC VARCHAR(48) OUTPUT
AS
DECLARE @OnlyC VARCHAR(48)
,@FIncCount INTEGER

-- 取出當(dāng)前單據(jù)流水號
SELECT @FIncCount=FIncCount FROM T_OrderList WHERE FID=@CodeC
-- 流水號加１
SELECT @FIncCount = @FIncCount +1

UPDATE T_OrderList SET FIncCount = @FIncCount WHERE FID= @CodeC

-- 組織各個(gè)編碼
SELECT @OnlyC = @CodeC? + '-' + @OnlyC
SELECT @CodeC = @OnlyC; SELECT @OnlyC AS FNumber
-- print @CodeC
GO

但這程方法不能在SELECT語句運(yùn)算出的結(jié)果中調(diào)用．如前面寫的SELECT P_OnlyC(FId)? as? 流水號,? *? from (select sum(..) from tab..) Tab1? 所以我想用函數(shù),但函數(shù)里又沒辦法執(zhí)行遞增流水號：

CREATE? FUNCTION? F_LT_GetOrderNo(@ID? int)?
AS? RETURN? VARCHAR(32)?
?????????? DECLARE? @OrderNo? int?
?????????? SELECT? @OrderNo? =? FIncCount? FROM? T_OrderList? WHERE? FID? =? @ID?
?????? --? 取得編號后，計(jì)數(shù)器加１?
?????????? UPDATE? T_OrderList? SET? FIncCount? =? FIncCount? +1? --? 函數(shù)中不允許執(zhí)行UPDATE?
?????????? RETURNS? @OrderNo
??????????
??????????
??????????
CREATE PROCEDURE n_GetBillNo
@billType? char(2),--單據(jù)類型
@BillOutNo? nvarchar(50) Output

?AS

Begin

declare @NowNO int
declare @date char(10)
declare @Symbol nvarchar(10)
declare @ErrorMsg nvarchar(200)

Set NoCount On
Begin Tran
--設(shè)定延時(shí)
SET LOCK_TIMEOUT 5000

--取當(dāng)前序號
Select @NowNO=fnumber,@Symbol=fCode,@date=Convert(char(8),fDate,112) from n_BillNo? With(xLock) where fcode=@BillType
if @@Error<>0
?? begin
?? Set @ErrorMsg='數(shù)據(jù)被鎖定，請求超時(shí)！'
?? Goto Failed
?? end
--是否是新的一月
if Convert(char(8),getdate(),112)<>@date
?Set @NowNo=1
?else
?Set @NowNo=@NowNo+1

--更新當(dāng)前序列號和設(shè)置最后更新日期
update n_BillNo set fNumber=@NowNO,fDate=GetDate() where fcode=@BillType
if @@Error<>0
?? begin
?? Set @ErrorMsg='更新外部序列號失敗！'
?? Goto Failed
?? end

--取得單號
Set @BillOutNo=lTrim(@SymBol)+Convert(char(8),GetDate(),112)+Right(('0000'+Convert(varchar,@NowNO)),4)

Goto Succeed

Failed:
? RaisError(@ErrorMsg,16,1)
? Rollback Tran?
? Set NoCount Off
? Return 1

Succeed:
? Commit Tran
? Set NoCount Off
? Return 0

End
GO