SQL Server paging stored procedure with input and output parameters (most efficient)

CREATE PROCEDURE Proc_page_withtopmax
(
@pageIndex int,--Page index
@pageSize int,--display number per page
@pageCount int output,--total pages, output parameters
@totalCount int output--Total number of bars
)
As
Begin
SET NOCOUNT on;

declare @sql nvarchar (1000)
Set @sql = ' select top ' from Tb_testtable where (id> (select Max (ID) from (select Top ' +str ((@pageIndex-1) * @pageSize) + ' ID from tb_testtable ORDER by id] as temp)) Order by ID '
Execute (@sql)

declare @sqlRecordCount nvarchar (1000)--a statement that gets the total number of records
Set @sqlRecordCount =n ' Select @recordCount =count (*) from tb_testtable '
DECLARE @recordCount INT--A variable that holds the total number of record bars
EXEC sp_executesql @sqlRecordCount, N ' @recordCount int output ', @recordCount output

if (@recordCount% @pageSize = 0)--if the total number of records can be divisible by the page size
Set @pageCount = @recordCount/@pageSize-The total number of pages is equal to the total record bar divided by the page size
Else--If the total number of records cannot be divisible by the page size
Set @pageCount = @recordCount/@pageSize + 1--Total pages equals total number of records divided by page size plus 1

Set @totalCount = @recordCount

SET NOCOUNT off;
End

--Execute the stored procedure in the database
declare @pageCount int, @totalCount int

exec proc_page_withtopmax 2,95955, @pageCount output, @totalCount output

Select ' Total pages: ' +str (@pageCount)
Select ' Total number of bars: ' +str (@totalCount)

C # code calls the paging stored procedure with input and output parameters

public static DataSet getrecordbypage (int pageSize, int. pageIndex, out int pagecount, out int totalcount)
{
DataSet ds = new DataSet ();
Try
{
using (SqlConnection conn = new SqlConnection (@ "server=;d atabase=data_test;uid=; pwd=;"))
{
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd. Parameters.Add (New SqlParameter ("@pageSize", SqlDbType.Int));
Cmd. Parameters.Add (New SqlParameter ("@pageIndex", SqlDbType.Int));
SqlParameter param = new SqlParameter ("@totalCount", SqlDbType.Int);
Param. Direction = ParameterDirection.Output;
Cmd. Parameters.Add (param);

SqlParameter param1 = new SqlParameter ("@pageCount", SqlDbType.Int);
param1. Direction = ParameterDirection.Output;
Cmd. Parameters.Add (param1);

Cmd. Parameters[0]. Value = pageSize;
Cmd. PARAMETERS[1]. Value = PageIndex;
Cmd. PARAMETERS[2]. Value = 0;
Cmd. PARAMETERS[3]. Value = 0;

Cmd.commandtype = CommandType.StoredProcedure;
Cmd.commandtext = "Proc_page_withtopmax";
Cmd.commandtimeout = 180;

SqlDataAdapter adapter = new SqlDataAdapter ();
Adapter. SelectCommand = cmd;

DataSet Source = new DataSet ();
Adapter. Fill (DS);

Object o = cmd. parameters["@totalCount"]. Value;
TotalCount = (o = = NULL | | o = = dbnull.value)? 0:system.convert.toint32 (o);

Object B = cmd. parameters["@pageCount"]. Value;
PageCount = (b = = NULL | | o = dbnull.value)? 0:system.convert.toint32 (b);
}
}
catch (SqlException e)
{
Throw e;
}
return DS;
}

}

SQL Server paging stored procedure with input and output parameters (most efficient)