Anforderung / Goal

Beispieldaten / Example data

Mit SQL "lead" Lücken finden / Find gaps with Sql "lead"

Datenbankprozedur für nächste freie Nummer / Stored procedure for next free number

Anforderung / Goal

Es sei eine SQL-Tabelle gegeben, die in einem bestimmten Feld alphanummerische Nummern-Definitionen enthält.

Diese Tabelle enthält Lücken im Nummernkreis.

Aufgabenstellung ist, möglichst performant die Lücken zu identifizieren und die nächste freie Nummer in einem bestimmten Bereich zu finden.

Consider an SQL table that contains alphanumeric number definitions in a particular field.

This table contains gaps in the number range.

The task is to identify the gaps as efficiently as possible and to find the next possible free number in a specific number range.

Beispieldaten / Example data

DECLARE @tbl TABLE ([MyTextBasedNumber] NVARCHAR(10))

INSERT INTO @tbl SELECT N'IND001'
INSERT INTO @tbl SELECT N'IND002'
INSERT INTO @tbl SELECT N'IND003'
INSERT INTO @tbl SELECT N'IND005'
INSERT INTO @tbl SELECT N'IND006'
INSERT INTO @tbl SELECT N'IND007'
INSERT INTO @tbl SELECT N'IND010'

INSERT INTO @tbl SELECT N'SWB001'
INSERT INTO @tbl SELECT N'SWB004'
INSERT INTO @tbl SELECT N'SWB007'
INSERT INTO @tbl SELECT N'SWB009'
INSERT INTO @tbl SELECT N'SWB010'

SELECT * FROM @tbl ORDER BY [MyTextBasedNumber]

Mit SQL "lead" Lücken finden / Find gaps with Sql "lead"

DECLARE @sPrefix nvarchar(3)

SET @sPrefix = N'IND'

DECLARE @lNewId INT

SET @lNewId = 0

-- Use SQL "lead" to find gaps!

;WITH cte1 ([Nr])
AS (
  SELECT CAST(SUBSTRING([MyTextBasedNumber], 4, 10) AS INT)
  FROM @tbl
  WHERE [MyTextBasedNumber] LIKE @sPrefix + N'%'
  AND ISNUMERIC(SUBSTRING([MyTextBasedNumber], 4, 10)) <> 0
  )
  ,cte2
AS (
  SELECT [Nr]
  ,lead([Nr]) OVER (
    ORDER BY [Nr]
    ) AS [NextNr]
  FROM cte1
  )
-- Use 
SELECT [Nr] + 1 AS [Next_free_nr]
  , [Nr] AS [gap_start]
  ,[NextNr] AS [gap_end]
  ,([NextNr] - [Nr] - 1) AS [gap_count]
FROM cte2
WHERE [Nr] < [NextNr] - 1

Datenbankprozedur für nächste freie Nummer / Stored procedure for next free number

CREATE PROCEDURE [dbo].[spGetNextFreeNr]
(
@sPrefix nvarchar(3)
,@sReturnNewId nvarchar(10) OUT
)
AS
BEGIN

  -- Find next free number within certain prefix data range

  SET @sReturnNewId = N''

  DECLARE @lNewId INT

  SET @lNewId = 0

  -- TODO: Set your field and table name!
  -- TODO: Set also the desired locking behaviour!
  -- TODO: Set formatting!

  ;WITH cte1 ([Nr])
  AS (
    SELECT CAST(SUBSTRING([MyTextBasedNumber], 4, 10) AS INT)
    FROM [dbo].[MyDataTable] WITH (READUNCOMMITTED) 
    WHERE [MyTextBasedNumber] LIKE @sPrefix + N'%'
    AND ISNUMERIC(SUBSTRING([MyTextBasedNumber], 4, 10)) <> 0
    )
    ,cte2
  AS (
    SELECT [Nr]
    ,lead([Nr]) OVER (
      ORDER BY [Nr]
      ) AS [NextNr]
    FROM cte1
    )
  SELECT TOP 1 @lNewId = [Nr] + 1 
  FROM cte2
  WHERE [Nr] < [NextNr] - 1

  SET @lNewId = ISNULL(@lNewId, 0)

  IF @lNewId > 0
  BEGIN

    SET @sReturnNewId = @sPrefix + FORMAT(@lNewId, N'000')

  END

  RETURN 0

END