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()
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
)
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
SELECT COUNT(1) as TotalRecords from #TMP
END