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

CREATE PROCEDURE Proc_page_withtopmax (@pageIndexint,--Page Index @pageSizeint,--number of displays per page @pageCountintOutput,--total pages, output parameters @totalCountintoutput--total number of articles) asbeginSetnocount on;declare @sql nvarchar ( +)Set@sql ='Select top * from Tb_testtable where (the 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 ( +) --The statement that gets the total number of recordsSet@sqlRecordCount =n'Select @recordCount =count (*) from tb_testtable'Declare @recordCountint--variable that holds the total number of records exec sp_executesql @sqlRecordCount, N'@recordCount int Output', @recordCount outputif(@recordCount% @pageSize =0) --if the total number of records can be divisible by the page sizeSet@pageCount = @recordCount/@pageSize--The total number of pages is equal to the total record bar divided by the page sizeElse--if the total number of records cannot be divisible by the page sizeSet@pageCount = @recordCount/@pageSize +1--The total number of pages is equal to the total record bar divided by the page size plus 1Set@totalCount =@recordCountSetnocount Off;end--the stored procedure is executed in the database declare @pageCountint, @totalCountintexec Proc_page_withtopmax2,95955, @pageCount output, @totalCount outputSelect 'Total Pages:'+Str (@pageCount)Select 'Total number of bars:'+str (@totalCount)

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

   Public StaticDataSet Getrecordbypage (intPageSize,intPageIndex, out intPageCount, out inttotalcount) {DataSet DS=NewDataSet (); Try            {                using(SqlConnection conn =NewSqlConnection (@"server=;d atabase=data_test;uid=; pwd=;") ) {SqlCommand cmd=NewSqlCommand (); Cmd. Connection=Conn; Cmd. Parameters.Add (NewSqlParameter ("@pageSize", SqlDbType.Int)); Cmd. Parameters.Add (NewSqlParameter ("@pageIndex", SqlDbType.Int)); SqlParameter param=NewSqlParameter ("@totalCount", SqlDbType.Int); Param. Direction=ParameterDirection.Output; Cmd.                    Parameters.Add (param); SqlParameter param1=NewSqlParameter ("@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= the; SqlDataAdapter Adapter=NewSqlDataAdapter (); Adapter. SelectCommand=cmd; DataSet Source=NewDataSet (); Adapter.                                     Fill (DS); Objecto = cmd. parameters["@totalCount"].                    Value; TotalCount= (O = =NULL|| o = = DBNull.Value)?0: System.Convert.ToInt32 (o); Objectb = cmd. parameters["@pageCount"].                    Value; PageCount= (b = =NULL|| o = = DBNull.Value)?0: System.Convert.ToInt32 (b); }            }            Catch(SqlException e) {Throwe; }            returnds; }    }

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