SQLServer创建最大单号

Create PROC [dbo].[spCreateMaxBillNo]
AS
  --1.1获取最大单号
  DECLARE @today VARCHAR(100) = ''
  DECLARE @maxBillNo VARCHAR(100) = ''
  DECLARE @ls VARCHAR(100) = '' --流水号

  SET @today     = CONVERT(VARCHAR(100), getdate(), 112)
  SET @today     = 'RKD' + @today   --前缀+日期
  SELECT @maxBillNo = max(bill_no)
  FROM xk_instorage_main     --某某单据主表
  WHERE bill_no LIKE @today + '%'

  IF (@maxBillNo IS NULL)
    BEGIN
      SET @maxBillNo = @today + '001'
    END
  ELSE
    BEGIN
      BEGIN TRY
        SET @ls        = right('000' + ltrim(cast(@ls AS INT) + 1), 3)
        SET @maxBillNo = @maxBillNo + @ls
      END TRY
      BEGIN CATCH
        SET @maxBillNo = @maxBillNo + @ls + '1'
      END CATCH
    END
    
  Select @maxBillNo
GO