CREATE PROCEDURE dbo.USP_GetNextCN
		(@CNID				int,	
		 @UserName			nvarchar(50),
		 @Deleted			bit,
		 @CNRecordReturned	int OUTPUT)

 AS

DECLARE @ErrorCode		int
DECLARE @NextCNID		int
DECLARE @CNDeleted		bit
DECLARE @SQLStatement	nvarchar(4000)
DECLARE @SQLWhere		nvarchar(100)

SET NOCOUNT ON

SET @NextCNID = '-1' 	-- set a condition to indicate to indicate that current CNID is last
SET @CNRecordReturned = '-1' -- set a condition that will indicate that no next CN record is available

IF @Deleted = 1
	SET @SQLWhere = ''
ELSE
	SET @SQLWhere = ' AND A.Deleted = 0 '

-- Declare a cursor to hold all the CNIDs this user is authorized to access
DECLARE @CUR_TEMP CURSOR

SET @SQLStatement = 	N'SET @CUR_TEMP = CURSOR SCROLL FOR SELECT DISTINCT A.CNID, A.Deleted FROM TAB_ccsNetCN AS A join TAB_ccsNetUserAccess AS B ON
			A.ProgID = B.ProgID and A.ProjID = B.ProjID and A.ContractID = B.ContractID
			WHERE B.ccsNetModule = ' +  char(39) + 'CN' +  char(39) + ' AND B.UserName = ' +  char(39) + @UserName + Char(39) + @SQLWhere + ' ORDER BY  A.CNID; OPEN @CUR_TEMP'


EXEC sp_executesql @SQLStatement,N'@CUR_TEMP CURSOR OUTPUT', @CUR_TEMP OUTPUT

FETCH FIRST FROM @CUR_TEMP INTO @NextCNID, @CNDeleted
WHILE @NextCNID <> @CNID	-- loop through cursor until you find current CNID record
BEGIN
	FETCH NEXT FROM @CUR_TEMP INTO @NextCNID, @CNDeleted
END
IF @CNDeleted = 1
	BEGIN
		WHILE @CNDeleted <> 0	-- loop through cursor until you find an active CNID record
		BEGIN
			FETCH NEXT FROM @CUR_TEMP INTO @NextCNID, @CNDeleted
			IF @@FETCH_STATUS = -1 -- it gets the last record and it is deleted. So the scrolling will be on the other direction
				BEGIN
					WHILE @CNDeleted <> 0	-- loop through cursor until you find an active CNID record
					BEGIN
						FETCH PRIOR FROM @CUR_TEMP INTO @NextCNID, @CNDeleted
					END
				END
		END
	END
ELSE
	BEGIN
		FETCH NEXT FROM @CUR_TEMP INTO @NextCNID, @CNDeleted --get the next CNID for this user
	END
IF @@FETCH_STATUS <> 0
	SET @NextCNID = '-1'
CLOSE @CUR_TEMP
DEALLOCATE @CUR_TEMP
SELECT @ErrorCode = @@Error
IF @ErrorCode = 0
BEGIN
	-- Get CN MASTER AND Child records for the next CN
	IF @NextCNID <> -1
		EXECUTE USP_GetThisCN @NextCNID, @UserName, 0, @CNRecordReturned = @CNRecordReturned OUT
END

SET NOCOUNT OFF
RETURN @ErrorCode
GO

declare @your_table table (your_id int)
declare @i int
set @i = 100
---------------------------------------------
-- populate @your_table
---------------------------------------------
while @i > 0
begin
	insert into @your_table
	select @i

	set @i = @i - 1
end

-- select * from @your_table

---------------------------------------------
-- find before / after values by @target_id
---------------------------------------------
declare	@target_id int
select	@target_id = 69

select top 3 your_id
from @your_table
where your_id >= (
		select max(your_id) 
		from @your_table
		where your_id < @target_id
)
order by your_id