Swiss Cheese!

/****** Object:  StoredProcedure [dbo].[USP_DisplaySequenceGap_Range]    Script Date: 04/02/2012 14:23:53 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,John Esraelo>
-- Create date: <Create Date,,201111032110>
-- Description:<Description,,USAGE: PASS FQN FOR A DB AND A FIELDNAME, ADD A RANGE OF FIELD VALUE AND RETURN GAPS>
-- USP_DisplaySequenceGap_Range 'main.dbo.PO','PO', 1000000, 9000000
-- =============================================
ALTER PROCEDURE [dbo].[USP_DisplaySequenceGap_Range]
  @Table as nvarchar(128) = 'MyDB.dbo.Employees'
 ,@Field as nvarchar(128) = 'ID'
 ,@From  as bigint = 5000
 ,@To    as bigint = 10000
AS
BEGIN
set nocount on 

declare @head bigint, @tail bigint, @pivot bigint
declare @RecKey bigint, @RecSeq bigint

create table #MyList
(
 RecKey bigint primary key identity(1,1) not null 
,RecSeq bigint null
)

truncate table #MyList 

declare @statement nvarchar(512)
set @statement = 
N'
;with NewSet as
(
select [' + convert(nvarchar(128), @Field)  + ']
from ' + @Table + ' 
where isnumeric([' + convert(nvarchar(128), @Field) + '])=1

)
insert into #MyList (RecSeq)
select convert(numeric, [' + convert(nvarchar(128), @Field) + ']) TheField 
from NewSet
order by [' + convert(nvarchar(128), @Field) + ']'
--print @statement
exec(@statement)

delete #MyList 
where RecSeq < @from or  RecSeq > @to 

declare MyCursor cursor for 
select RecKey, RecSeq from #MyList

open MyCursor

Fetch next from MyCursor
into @RecKey, @RecSeq

set @head = @RecSeq
set @tail = @head
set @pivot = @RecKey

declare @outfile table 
( Header nvarchar(128), Msg nvarchar(128))

while @@fetch_status = 0
begin
if @tail >  @head + 1 
  insert into @outfile (header, msg)
select 'Missing/Gap in Sequence:: ' ,  
case @tail-@head 
when 2 then convert(nvarchar(32), @head+1)
elseconvert(nvarchar(32), @head+1) + ' To ' + convert(nvarchar(32), @tail -1)
end

Fetch next from MyCursor
into @RecKey, @RecSeq
set @head = @tail   
set @tail = @RecSeq
set @pivot = @RecKey
end

select * from @outfile 

drop table #MyList
close MyCursor
deallocate MyCursor
END