Union on SP with custom paging routine

At present i have the following SP code

SELECT TOP(@PageSize) * FROM
(
SELECT RowID = ROW_NUMBER() OVER (ORDER BY datentime DESC),
RefID, InboxId, MessageType, SenderID,
RecieverID, [Subject], Body, DatenTime,
TotalRows=Count(inboxid) OVER()

FROM Inbox_Messages
)
A WHERE A.RowID > ((@PageIndex-1)*@PageSize) order by datentime DESC

I want to introduce another table that has same column included as Inbox_Messages so that the result set displays both table data.

I understand that this can be achieved using a UNION. The problem is there is two extra entries namely TotalRows and RowID therefore the two tables do not have matching columns.So how may i reconfigure so that it returns correct paged data?

New table addition

SELECT

RefID,
InboxID,
MessageType,
SenderID,
RecieverID,
[Subject],
Body,
Datentime

FROM Inbox_Return

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
you first need to join or union whatever you prefer and then you should do paginationation.
Meaning something like below
alter procedure yourSP
-- your parameters
@NumberofRecords INT = 20,
,@NumberOfPages INT = 0 OUT
,@TotalNumberOfRecords INT = 0 OUT
as
begin
DECLARE @offsetrow INT = 0;
IF (@pageNo > 1)
BEGIN
SET @offsetrow = (@pageNo - 1) * @NumberofRecords;
END;
select * from
--multiple joins or Union
order by 1
DESC OFFSET @offsetrow ROWS
FETCH NEXT @NumberofRecords ROWS ONLY;
end