SQL - Select records/rows with paging and result count

You'll notice that I run two queries on the database, one to find out how many results there are, and another to get the actual records/rows.

I've tested other queries with only one select, but they were around the same in execution time as the below example with two selects.

The code given in the example below will create a stored procedure called getSearch, that, when run, will return the paged result set and a parameter that defines the total records/rows.

CREATE PROCEDURE [dbo].[getSearch]
	@RecordId int,
	-- Paging
	@PageNumber int = 1,
	@PageSize int = 50,
	@SortOrder int = 0
AS
SET NOCOUNT ON;

DECLARE @RowStart int
SET @RowStart = ((@PageNumber - 1) * @PageSize)

-- Find total search result count
DECLARE @RowCount int
SELECT
	@RowCount = COUNT(*)
FROM (
	SELECT
		TOP(400) recordId
	FROM MyTable
	WHERE
		-- Where result is not current member
		(recordId <> @RecordId) 
) AS T

SELECT
	recordId, myOtherColumnNames
FROM MyTable
WHERE
	(recordId <> @RecordId)
ORDER BY
	recordId DESC,
	-- Within specified page
	OFFSET @RowStart ROWS
	FETCH NEXT @PageSize ROWS ONLY

RETURN @RowCount

You might also see that I'm limiting my result count to 400 records/rows so I don't get too many pages in my basic paging bar, you can take this out if you want the full count returned.

Using a bit of C# magic, you're then able to run the stored procedure using the normal method of reading the returned results or rows or records or whatever you choose to call them. The clever bit comes when you want to get the total records parameter. Add the following parameter to your SqlCommand object:

objCmd.Parameters.Add("@RowCount", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;

Where objCmd is a SqlCommand object, you can call the parameter anything you want as long as the Direction is set to ReturnValue.

Now you have to read your SqlDataReader and when you're finished with it, close it, because you can only read the parameter after the reader has been closed. Once you've done that, you can get the value using the below code:

Int32 intRowCount = (Int32)objCmd.Parameters["@RowCount"].Value;

And that's it, you've just retrived a paged set of records, read them and then read the total row count. With all of this, you can render your set of results within your application and calculate how many pages there are so that you can render a paging bar.