SQL stored procedure paging query--support only MSSQL2005 and above

 Use [QSM]GO/** * * * object:storedprocedure [dbo].    [Up_sysgetlistbypage] Script date:04/12/2017 17:47:02 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGO-- =============================================--Author: <Author,,Name>--Create Date: <create date,,>--Description: Paging stored procedure (only technical MSSQL2005 and above)-- =============================================ALTER PROCEDURE [dbo].[Sp_getlistbypage]    @Select nvarchar( +)--field to query (select included),@From nvarchar( +)--table name (included from),@WhereSql nvarchar( +)--query criteria (where 1=1 is included),@GroupBy nvarchar( -)--grouping (with GROUP by),@Having nvarchar( -)--filter conditions (with having),@OrderBy nvarchar( -)--sort field (with order by),@IsDesc bit=0                --Sort: 0=asc,1=desc,@PageIndex int = 0            --page index: 0= 1th page,@PageSize int = Ten            --page Capacity: Default 10 rows,@IsRecord bit =0            --0 = DataSet query, 1 = Fetch record Count asBEGIN    SETNOCOUNT on; DECLARE @sqlFrom nvarchar( the);--fields and tables to query    DECLARE @pagedSql nvarchar(Max);--the total SQL statement after stitching        SET @sqlFrom=N' from' + @From; IF(LEN(@WhereSql)>0)SET @sqlFrom+='where 1=1' + @WhereSql; IF(LEN(@GroupBy)>0)SET @sqlFrom+='Group by' + @GroupBy; IF(LEN(@Having)>0)SET @sqlFrom+=' having' + @Having; IF(@IsDesc=1)SET @OrderBy+='DESC'; SET @pagedSql=N'With Pagedlist as'        +' ( '        +'Select top Percent'+@Select        +      ', Row_number () over (order by'+@OrderBy+') as Mydata_rownumber'        +         @sqlFrom         +' ) '        +'SELECT *'        +'From pagedlist'        +'where Mydata_rownumber between' + STR(@PageIndex*@PageSize+1)         +                           ' and' + STR(@PageIndex*@PageSize+@PageSize) ; IF @IsRecord =1        SET @pagedSql = 'Select COUNT (1) as [RowCount] from' + @From +'where 1=1' + @WhereSql ; EXEC(@pagedSql)    --SELECT @pagedSql-generated statementsEND

SQL stored procedure paging query--support only MSSQL2005 and above