Here is a little stored procedure i made out of this that will return a list of gaps including missing 1-x (or 0-x or whatever-x) or
return the first available number i a sequence. Njoy :hehe:
-- Drop procedure if it exists
IF EXISTS (SELECT * FROM [sys].[objects] WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[FindGap]') AND [type] IN (N'P', N'PC')) DROP PROCEDURE [dbo].[FindGap]
GO
-- Create procedure
CREATE PROCEDURE [dbo].[FindGap]
@strFindGapTable VARCHAR(254),
@strFindGapField VARCHAR(254),
@binFindGapLower BIGINT = 1,
@intFindGapMode INT = 0
AS
-- +---------------------------------------------------------------------------+
-- | |
-- | Type : SQL Procedure |
-- | Name : FindGap |
-- | Version : 1.1 |
-- | Creator : Martin Grape, ActionBase, +46 73 391 88 89 |
-- | Info : Returns first available numer in a field of sequence or |
-- | : displays a list of available gaps and number of gaps. |
-- | Input : @strFindGapTable -> Name of table to run on |
-- | : @strFindGapField -> Name of field |
-- | : @strFindGapLower -> Lower value to find gap from |
-- | : @strFindGapMode -> 0=All gaps, 1=Next available number |
-- | Example : EXEC FindGap 'yourtable', 'sysid', 1, 1 |
-- | History : 2011-07-03 Created v1.0 |
-- | : 2011-07-03 Added check for all numbers in sequence v1.1 |
-- | : |
-- | |
-- +---------------------------------------------------------------------------+
-- Declare variables
DECLARE @strFindGapSQL AS NVARCHAR(2000);
DECLARE @strFindGapPar AS NVARCHAR(2000);
DECLARE @binFindGapMin AS BIGINT;
DECLARE @binFindGapMax AS BIGINT;
DECLARE @binFindGapCount AS BIGINT;
DECLARE @binFindGapFirst AS BIGINT;
-- Make SQL to find min and max values
SET @strFindGapSQL = N'SELECT @binMinVal = MIN(' + @strFindGapField + '), @binMaxVal = MAX(' + @strFindGapField + '), @binCount = COUNT(*) FROM ' + @strFindGapTable;
SET @strFindGapPar = N'@binMinVal BIGINT OUTPUT, @binMaxVal BIGINT OUTPUT, @binCount BIGINT OUTPUT';
-- Get values into variables
EXEC SP_EXECUTESQL @strFindGapSQL, @strFindGapPar, @binFindGapMin OUTPUT, @binFindGapMax OUTPUT, @binFindGapCount OUTPUT;
-- Check mode
IF @intFindGapMode = 1
-- Next available sequence number
BEGIN
-- Check if min is bigger than supplied lower, then take that value
IF @binFindGapMin > @binFindGapLower
BEGIN
RETURN @binFindGapLower;
END
-- Check if max is smaller than supplied lower, then take that value
ELSE IF @binFindGapMax < @binFindGapLower
BEGIN
RETURN @binFindGapLower;
END
-- Check if max is the same as supplied lower, then return + 1
ELSE IF @binFindGapMax = @binFindGapLower
BEGIN
RETURN @binFindGapLower + 1;
END
-- Check if no existing gaps, return higest + 1
ELSE IF @binFindGapCount = @binFindGapMax - @binFindGapMin + 1
BEGIN
RETURN @binFindGapMax + 1
END
-- Get first available number
ELSE
BEGIN
-- Create SQL to get next value
SET @strFindGapSQL = N'SELECT TOP 1 @binMinVal = LastSysId + 1 FROM (SELECT (SELECT TOP 1 ' + @strFindGapField + ' FROM ' + @strFindGapTable + ' WHERE ' + @strFindGapField + ' < a.' + @strFindGapField + ' ORDER BY ' + @strFindGapField + ' DESC) AS LastSysId, a.' + @strFindGapField + ' AS NextSysId FROM ' + @strFindGapTable + ' AS a LEFT JOIN ' + @strFindGapTable + ' AS b ON a.' + @strFindGapField + ' = b.' + @strFindGapField + ' + 1 WHERE b.' + @strFindGapField + ' IS NULL) AS a WHERE LastSysId IS NOT NULL ORDER BY 1';
SET @strFindGapPar = N'@binMinVal BIGINT OUTPUT';
-- Get value into variable
EXEC SP_EXECUTESQL @strFindGapSQL, @strFindGapPar, @binFindGapFirst OUTPUT;
-- Return value
RETURN @binFindGapFirst;
END
END
ELSE
-- All available sequence numbers as a list
BEGIN
-- Check if first value is bigger than supplied lower, then add a row
IF @binFindGapLower < @binFindGapMin
BEGIN
-- Add a union from supplied lower to the first actual value
SET @strFindGapSQL = N'SELECT ' + CAST(@binFindGapLower AS NVARCHAR) + ' AS GapFrom, ' + CAST(@binFindGapMin - 1 AS NVARCHAR) + ' AS GapTo, ' + CAST(@binFindGapMin - @binFindGapLower AS NVARCHAR) + ' AS GapSize UNION ';
END
ELSE
BEGIN
-- Reset variable
SET @strFindGapSQL = '';
END
-- Check if to add
SET @strFindGapSQL = @strFindGapSQL + 'SELECT LastSysId + 1 AS GapFrom, NextSysId - 1 AS GapTo, NextSysId - (LastSysId + 1) AS GapSize FROM (SELECT (SELECT TOP 1 ' + @strFindGapField + ' FROM ' + @strFindGapTable + ' WHERE ' + @strFindGapField + ' < a.' + @strFindGapField + ' ORDER BY ' + @strFindGapField + ' DESC) AS LastSysId, a.' + @strFindGapField + ' AS NextSysId FROM ' + @strFindGapTable + ' AS a LEFT JOIN ' + @strFindGapTable + ' AS b ON a.' + @strFindGapField + ' = b.SysId + 1 WHERE b.' + @strFindGapField + ' IS NULL) AS a WHERE LastSysId IS NOT NULL ORDER BY 1';
-- Run the select
EXEC (@strFindGapSQL);
-- Return also the rowcount
RETURN @@ROWCOUNT;
END
-- +------------------+
-- | END OF PROCEDURE |
-- +------------------+
GO
-- +---------+
-- | Testing |
-- +---------+
-- Drop test table if it existst
IF EXISTS (SELECT * FROM [sys].[objects] WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[yourtable]') AND [type] IN (N'U')) DROP TABLE [dbo].[yourtable]
GO
-- Add dummy data
SELECT 2 AS SysId INTO yourtable UNION
SELECT 6 AS SysId UNION
SELECT 8 AS SysId UNION
SELECT 9 AS SysId UNION
SELECT 11 AS SysId;
-- Example 1: Adds a row with next available number starting on 0 , displays the result table and selects next available number
DECLARE @binFindGapReturn BIGINT;
EXEC @binFindGapReturn = FindGap 'yourtable', 'sysid', 0, 1;
INSERT INTO yourtable (SysId) SELECT @binFindGapReturn
SELECT * FROM yourtable ORDER BY 1
EXEC @binFindGapReturn = FindGap 'yourtable', 'sysid', 0, 1;
SELECT @binFindGapReturn AS NextValue;
GO
-- Example 2: Returns all the gaps starting on 1 as a resultset and returns number of gaps
DECLARE @binFindGapReturn BIGINT;
EXEC @binFindGapReturn = FindGap 'yourtable', 'sysid', 1, 2;
SELECT @binFindGapReturn AS NoOfGaps;
GO
-- Example 3: Fill missing sequences to get max + 1
INSERT INTO yourtable SELECT 1
INSERT INTO yourtable SELECT 3
INSERT INTO yourtable SELECT 4
INSERT INTO yourtable SELECT 5
INSERT INTO yourtable SELECT 7
INSERT INTO yourtable SELECT 10
DECLARE @binFindGapReturn BIGINT;
EXEC @binFindGapReturn = FindGap 'yourtable', 'sysid', 0, 1;
SELECT @binFindGapReturn AS NextValue;
GO
-- Drop test table if it existst
IF EXISTS (SELECT * FROM [sys].[objects] WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[yourtable]') AND [type] IN (N'U')) DROP TABLE [dbo].[yourtable]
GO