'How to return an object with all rows and count of all rows
I have a stored procedure that returns 20 rows based on page number:
CREATE PROCEDURE SP_GET_ALL
@PAGE INT
AS
SELECT *
FROM Item
ORDER BY ID
OFFSET (@PAGE - 1) * 20 ROWS
FETCH NEXT 20 ROWS ONLY
When I consume the API the result is like this
[
{id:1, name:"name1"},
{id:2, name:"name2"},
{id:3, name:"name3"},
{id:4, name:"name4"},
{id:5, name:"name5"},
{id:6, name:"name6"},
{id:7, name:"name7"},
....
]
I want to return the total count of all rows in the same query to set up web pagination. for example
{
count: 1000,
rows:[
{id:1, name:"name1"},
{id:2, name:"name2"},
{id:3, name:"name3"},
{id:4, name:"name4"},
{id:5, name:"name5"},
{id:6, name:"name6"},
{id:7, name:"name7"},
....
]
}
I don't want to do two requests to get the rows and the total count of all the rows.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
