34,594
社区成员
发帖
与我相关
我的任务
分享
CREATE procedure [dbo].[GetBillNo]
@Billtypeid int,
@Projectid int,
@FBillno nvarchar(20) OUTPUT
as
BEGIN TRANSACTION
Begin
Declare @TmpID nvarchar(10)
Declare @billno nvarchar(10)
Declare @qianz nvarchar(10)
SET @TmpID = (SELECT FID FROM t_BillCodeRule WITH(READUNCOMMITTED)
WHERE fbilltypeid=@Billtypeid and fprojectid=@Projectid)
select @qianz=Fprojectval FROM t_BillCodeRule WITH(READUNCOMMITTED)
WHERE fbilltypeid=@Billtypeid and fprojectid=1
update t_billcoderule with(rowlock) set fprojectval = fprojectval+1,
flength=case when (flength-len(fprojectval)) >= 0 then flength else len(fprojectval) end where FID = @TmpID
Update ICBillNo with(rowlock) Set FCurNo = (select top 1 isnull(fprojectval,1)
from t_billcoderule where FID = @TmpID) where fbillid = @Billtypeid
select @billno=left('0000000000',Flength-len(Fprojectval))+Fprojectval from t_billcoderule where FID = @TmpID
Update ICBillNo with(rowlock) Set FDesc = @billno Where FBillID = @Billtypeid
select @FBillno=@qianz+@billno
End
commit TRANSACTION
select *
from 表 with(tablock,xlock)
DECLARE @i int,@OrderNo nvarchar(20),@newOrderID int,@newOrderPaymentID int;
SET @OrderNo='BX'+replace(CONVERT(varchar(7),@OrderDate,120),'-','')
IF exists(SELECT 1 FROM RoomBookingOrder WHERE OrderNo like '%'+@OrderNo+'%')
BEGIN
SELECT @i=cast(MAX(right(OrderNo,3)) as int)+1 FROM RoomBookingOrder WHERE OrderNo like '%'+@OrderNo+'%'
SET @OrderNo=@OrderNo+'-'+STUFF('000', 4-len(@i), len(@i), @i)
END
ELSE
SET @OrderNo=@OrderNo+'-001'
SELECT @OrderNo