How to not allow users to view same records

See more:

SQL

hi,

How to not allow users to view same records

e.g.
in table I have following records
a
b
c
d
e
f
g
h

I want to retrieved records by page
say 4 records per page

user x picks
a
b
c
d

Now user y should not pick any of the above
e
f
g
h

user x processes a record say record b
now he should see
a
e
c
d

and user y should see
f
g
h
i

how can I accomplish this, is there any built in way in mssql?

What I have tried:

wrote a sql stored procedure for this,

using adventureworks db
sql-server-samples/samples/databases/adventure-works at master · Microsoft/sql-server-samples · GitHub[^]

<pre>ALTER Procedure [dbo].[AllocateRecords2]
(@UserID INT, 
@PageSize INT,
@PageNumber INT
)
AS
BEGIN
	DECLARE @Today DATETIME
	SET @Today = GETDATE()

	--de-allocated expired items
	--TRUNCATE TABLE AllocatedRecords
	DELETE FROM AllocatedRecords
	WHERE IsProcessed = 0 AND AllocatedToUser = @UserID
	AND DATEDIFF(minute, @Today, AllocatedDate) > 5

	DECLARE @Draw INT
	SET @Draw = 10

	DECLARE @PoolSize INT
	SET @PoolSize = @PageSize
	
	DECLARE @CurrentRecords INT
	SELECT @CurrentRecords = Count(*) from AllocatedRecords
	WHERE AllocatedToUser = @UserID
	AND IsProcessed = 0

	IF @CurrentRecords = 0
	BEGIN
		SET @Draw = @PoolSize
	END
	ELSE IF @CurrentRecords < @PoolSize
	BEGIN
		SET @Draw = @PoolSize - @CurrentRecords
	END
	ELSE IF @CurrentRecords >= @PoolSize
	BEGIN
		SET @Draw = 0
	END

	SELECT ProductID as ReferenceID,0 as IsProcessed,@UserID as AllocatedToUser, GETDATE() as AllocatedDate,null as ProcessedDate
	INTO #TMP
	from [Production].[Product]
	WHERE ProductID not in
	(
		SELECT ReferenceID from AllocatedRecords
		WHERE IsProcessed = 1
	)

	--deallocate all not processed
	--DELETE FROM AllocatedRecords
	--WHERE IsProcessed = 0 AND AllocatedToUser = @UserID
	
	SELECT * INTO #TMP2
	FROM    ( SELECT ROW_NUMBER() OVER ( ORDER BY AllocatedDate ) AS RowNum, 
				ReferenceID, IsProcessed, AllocatedToUser, AllocatedDate, ProcessedDate
			  FROM #TMP
			) AS RowConstrainedResult
	WHERE RowNum BETWEEN ((@PageNumber - 1) * @PageSize + 1)
        AND (@PageNumber * @PageSize)
	ORDER BY RowNum
	
	INSERT INTO AllocatedRecords
	SELECT TOP(@Draw) ReferenceID, IsProcessed, AllocatedToUser, AllocatedDate, ProcessedDate
	FROM #TMP2
	WHERE ReferenceID NOT IN
	(
		SELECT ReferenceID FROM AllocatedRecords
	)

	SELECT * FROM AllocatedRecords
	WHERE AllocatedToUser = @UserID and IsProcessed = 0
	--update AllocatedRecords SET IsProcessed = 1 where referenceid=975

	SELECT COUNT(1) as TotalRecords from #TMP
END