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