创建一个订单号自动递增获取存储过程，供生成订单时调用 1）采购订单 GUA，销售订单 XSA 单号示例：GUA000001，GUA 为订单标识，000001 为递增值,以SQL server为例

在SQL Server中创建一个订单号自动递增获取存储过程，可以结合订单标识符（如GUA或XSA）和递增值，你可以这样做：

CREATE PROCEDURE dbo.GenerateOrderNumber(@OrderType CHAR(3), @Prefix VARCHAR(5))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CurrentSequence INT;
    SET @CurrentSequence = (SELECT TOP(1) OrderNumber
                            FROM (
                                SELECT OrderNumber,
                                       ROW_NUMBER() OVER(PARTITION BY OrderType ORDER BY OrderNumber DESC) as RowNum
                                FROM [YourDatabase].[dbo].[Orders]
                            ) subquery
                            WHERE OrderType = @OrderType AND OrderNumber LIKE @Prefix + '%'
                            AND RowNum = 1);

    IF @CurrentSequence IS NULL
    BEGIN
        SET @CurrentSequence = 1; -- 初始化递增值，如果找不到有效的序号则从1开始
    END

    SET @CurrentSequence = RIGHT('00000' + CAST(@CurrentSequence AS VARCHAR(6)), 6); -- 补足六位数的顺序号

    INSERT INTO [YourDatabase].[dbo].[Orders] (OrderType, OrderNumber)
    VALUES (@OrderType, @Prefix + RIGHT('00000' + CAST(@CurrentSequence AS VARCHAR(6)), 6));

    SELECT @CurrentSequence AS NextOrderNumber;
END;

此存储过程接受订单类型（GUA或XSA）和前缀（GUA或XSA），查找当前可用的最大序号，如果不存在，则初始化为1。最后生成并插入新的订单号到Orders表中，同时返回新的订单号给调用者。