生成一个订单流水单号

USE [DEV_WXT]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetServiceNo]    Script Date: 08/19/2010 17:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







ALTER    PROCEDURE [dbo].[usp_GetServiceNo](
@strSiteCode nvarchar(30),
@strServiceNo char(20) output)


AS

/*
     
SELECT * FROM sys_site

declare @strNo varchar(30)
exec usp_GetServiceNo 'SITEA',@strNo output



*/


DECLARE @YearCode char(2)
DECLARE @MonthCode char(2)
DECLARE @DayCode char(2)
DECLARE @SerialNum char(4)


DECLARE @TempYearCode char(2)
DECLARE @TempMonthCode char(2)
DECLARE @TempDayCode char(2)

DECLARE @TempDateFlag char(1)
DECLARE @TempSerialNum int

declare @shortName char(3)



if exists( select 1 from SYS_Site where SiteCode = @strSiteCode )
    begin 
        
        -- 1、得到SQL服务器时间：年月，两位年，两位月。
        set @YearCode = substring(convert(char(4),year(getdate())),3,2)
        set @MonthCode = substring('0'+convert(char(2),month(getdate())),len(convert(char(2),month(getdate()))),2)
        set @DayCode = substring('0'+convert(char(2),day(getdate())),len(convert(char(2),day(getdate()))),2)
        
        -- 2、得到数据库中的年月日
        select @TempYearCode = Year1,@TempMonthCode = Month1,@TempDayCode=Day1,@shortName=ShortCode 
            from SYS_Site where SiteCode = @strSiteCode
        --print @TempYearCode
        --print @YearCode
        if @YearCode = @TempYearCode and @MonthCode=@TempMonthCode and @TempDayCode=@DayCode        
            begin
                set @TempDateFlag = 'Y'
            end
        else
        -- 与当前年份不同。
            begin
                set @TempDateFlag = 'N'
            end
        
        --print @TempDateFlag

        if @TempDateFlag = 'N'
        begin
            --print '和服务器当前月份不相等，重置月份和流水号。'
            set @TempSerialNum = 0
        
            update SYS_Site 
                set Year1 = @YearCode, Month1 = @MonthCode,Day1=@DayCode, ServiceNo = 0
                where  SiteCode = @strSiteCode
        end
        
        --print '取流水号，更新流水号。'
        select @TempSerialNum = ServiceNo from SYS_Site where SiteCode = @strSiteCode
        
        set @TempSerialNum = @TempSerialNum + 1
        set @SerialNum = substring('000' + convert(char,@TempSerialNum),len(convert(char,@TempSerialNum)),4)
        
        update SYS_Site  set ServiceNo = @TempSerialNum where SiteCode = @strSiteCode
        
        -- 6、组合得到 Service No
        set @strServiceNo = rtrim(@shortName) + @YearCode +  @MonthCode + @DayCode +  @SerialNum
        set @strServiceNo = upper(ltrim(rtrim(@strServiceNo)))

        --print @SerialNum
    end 
    
else

    begin
        -- 客户不存在的情况，生成一个错误单号。
        set @strServiceNo = ''           
    end

--return value
select @strServiceNo