SQL Server paging stored procedures

----------------------the paging stored procedure--------------------------------------------------------------------------------------------- -------CREATE PROCEDURE [dbo]. [Proc_page] @PageIndex int = 1,                    --current page @pagesize int =                    --number of records per page (page size) @Table nvarchar,   &NB Sp          --table or view name, or even nested sql: (Select * from Tab Where id>1000) Tab@field nvarchar (200) = ' * ' ,          --returns the recordset field name, "," separated by default is "*" @OrderBy nvarchar = ' ID ASC ',    --Collation @filte R nvarchar ($),                --Filter conditions @maxpage int output,                  --Execution Results-1 error, 0 false, maxpage true@totalrow int output,                  --Total records   @Descript varchar (+) output      --result description ASbeginset ROWCOUNT @PageSize; Set @Descript = '; Set @MaxPage = 0;-------------------End of Detection----------------Begin try --Integrate sql  Declare @SQL nvarchar (4000) @ Portion nvarchar (4000);  Set @Portion = ' row_number () over (ORDER by ' + @OrderBy + ') as ROWNUM from ' + @Table; &nb Sp Set @Portion = @Portion + (case if LEN (@Filter) >= 1 then (' Where ' + @Filter + ') as tab ') ELSE (') as tab ') END); nbsp Set @SQL = ' Select TOP (' + CAST (@PageSize as nvarchar (8)) + ') ' + @Field + ' from (Select ' + @Field + ', ' + @Portion; &nb Sp Set @SQL = @SQL + ' Where tab. ROWNUM > ' + CAST ((@PageIndex-1) * @PageSize as nvarchar (8)) + ' ORDER by ' + @OrderBy;  PRINT @SQL; -execute SQL, fetch Current page recordset   Execute (@SQL); --Consolidated sql  Set @SQL = ' Set @Rows = (select MAX (ROWNUM) from (SELECT ' + @Portion + ') '; -execute SQL, Fetch maximum page number   Execute sp_executesql @SQL, N ' @Rows int output ', @TotalRow output;  Set @MaxPage = (CA SE when (@TotalRow% @PageSize) <>0 then (@TotalRow/@PageSize + 1) ELSE (@TotalRow/@PageSize) END); End Trybegin catch --catch error   Set @MaxPage = -1;  Set @Descript = ' ERROR line: ' + CAST (Error_line () as Varc Har (8)) + ', error number: ' + CAST (error_number () as varchar (8) + ', error message: ' + error_message ();  Return; End catch;--execution successful return; Endgo

SQL Server paging stored procedures