----------------------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