'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