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)
					else		convert(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