GetSerialNoByOrders
来源:互联网 发布:Deserve it. 编辑:程序博客网 时间:2024/04/29 21:30
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[GetSerialNoByOrders]
(
@NowDate as datetime
)
RETURNS char(12)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result char(12)
DECLARE @Prefix as char(8)
DECLARE @Suffix as char(4)
DECLARE @MaxNum as int
set @Prefix = dbo.FormatDate2String(@NowDate)
-- Add the T-SQL statements to compute the return value here
select @MaxNum = count(id) + 1
from dbo.Orders
where datediff(d,CheckinTime,@NowDate) = 0
and Status <> 0
if @MaxNum = 0
begin
set @Suffix = '0001'
end
else
begin
DECLARE @Length as int
DECLARE @PrefixAddition as varchar(4)
set @Length = 4 - len( (@MaxNum) )
set @PrefixAddition = ''
while(@Length > 0)
begin
set @PrefixAddition = '0' + @PrefixAddition
set @Length = @Length - 1
end
set @Suffix = @PrefixAddition + cast ( @MaxNum as varchar)
end
set @Result = @Prefix + @Suffix
-- Return the result of the function
RETURN @Result
END
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[GetSerialNoByOrders]
(
@NowDate as datetime
)
RETURNS char(12)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result char(12)
DECLARE @Prefix as char(8)
DECLARE @Suffix as char(4)
DECLARE @MaxNum as int
set @Prefix = dbo.FormatDate2String(@NowDate)
-- Add the T-SQL statements to compute the return value here
select @MaxNum = count(id) + 1
from dbo.Orders
where datediff(d,CheckinTime,@NowDate) = 0
and Status <> 0
if @MaxNum = 0
begin
set @Suffix = '0001'
end
else
begin
DECLARE @Length as int
DECLARE @PrefixAddition as varchar(4)
set @Length = 4 - len( (@MaxNum) )
set @PrefixAddition = ''
while(@Length > 0)
begin
set @PrefixAddition = '0' + @PrefixAddition
set @Length = @Length - 1
end
set @Suffix = @PrefixAddition + cast ( @MaxNum as varchar)
end
set @Result = @Prefix + @Suffix
-- Return the result of the function
RETURN @Result
END