SQL Server生成订单号

下面就以插入订单为例，其中有一段代码是可提取到一个单独的存储存储过程中的，这样可增加通用性。

 

--插入客户订单
CREATE PROCEDURE [dbo].[Proc_InsertOrderInfo]
(
@UserID int,
@ProductID int,
@PurchaseNum int,
@PurchaseAmt numeric(18,2),
@TotalPoints int,
@ShipFee numeric(18,2),
@RealName varchar(50),
@UserEmail varchar(50),
@UserPhone varchar(50),
@UserMobile varchar(20),
@UserAddress nvarchar(100),
@UserZipCode varchar(20),
@OrderProvince varchar(50),
@OrderCity varchar(50),
@UserMessage ntext,
@ShipType int,
@PayType int,
@OrderRandom varchar(60),
@OrderID int outPut    --输出
)

AS

SET NOCOUNT ON

--编号年月部分
--DECLARE @YM char(6)
--SET @YM = CONVERT(char(6),GETDATE(),12)
DECLARE @YMD char(8)
SET @YMD = CONVERT(char(8),GETDATE(),112)
--单据字头
--DECLARE @DJHH char(8)
--SET @DJHH = 'ST' + @YM

--找编号的基值（方法：用当天的编号到排序取最大的一个。如当前最大编号为000001那么它的基值为1，这样就能得出下个编号为000002）
--where left(OrderNumber,8) = @YMD
DECLARE @MNO INT --当天最大的单据号(数值)
SET @MNO = 1
select TOP 1 @MNO = CONVERT(int,RIGHT(OrderNumber,6))+1 from tb_OrderInfo Order by OrderNumber desc

DECLARE @OrderNumber char(14)

--扩充成6位十进制编号(最大可显示999999)
If @MNO <= 999999
If @MNO <= 99999
IF @MNO <= 9999
IF @MNO <= 999
IF @MNO <= 99
IF @MNO <= 9
SET @OrderNumber = @YMD + '00000' + CONVERT(char(1),@MNO)
ELSE
SET @OrderNumber = @YMD + '0000' + CONVERT(char(2),@MNO)
ELSE
SET @OrderNumber = @YMD + '000' + CONVERT(char(3),@MNO)
ELSE
SET @OrderNumber = @YMD + '00' + CONVERT(char(4),@MNO)
ELSE
SET @OrderNumber = @YMD + '0' + CONVERT(char(5),@MNO)
ELSE
SET @OrderNumber = @YMD + CONVERT(char(6),@MNO)
ELSE
SET @OrderNumber = @YMD + '999999'

--插入订单
INSERT INTO tb_OrderInfo (UserID,ProductID,PurchaseNum,OrderNumber,PurchaseAmt,TotalPrice,TotalPoints,ShipFee,RealName,UserEmail,UserPhone,UserMobile,UserAddress,UserZipCode,OrderProvince,OrderCity,OrderRandom,UserMessage,ShipType,PayType)
 VALUES (@UserID,@ProductID,@PurchaseNum,@OrderNumber,@PurchaseAmt,(@PurchaseAmt+@ShipFee),@TotalPoints,@ShipFee,@RealName,@UserEmail,@UserPhone,@UserMobile,@UserAddress,@UserZipCode,@OrderProvince,@OrderCity,@OrderRandom,@UserMessage,@ShipType,@PayType)
Select @OrderID=@@identity