SQL SERVER Universal Paging stored procedures, either of which you choose

Write in front

Starting with SQL Server 2005, provided the Row_number () function, using the function generated by the index to handle paging, according to normal logical thinking is to pass pageindex and pagesize to complete the paging, the front end and I communicated yesterday, They use JQuery.DataTable.js plug-ins, and through the company's bottom-level package, pageindex need to change a bit, become pageindex*pagesize to preach. That is to show 30 per page count, the first pass 0, the second pass 30 so to calculate, I am also drunk.

1, transmission pageindex and pagesize

CREATE PROC [dbo].[common_pagelist](@tab nvarchar(Max),---table name@strFld nvarchar(Max),--Field String@strWhere varchar(Max),--Where Condition@PageIndex int,--Page Number@PageSize int,--number of records to hold per page@Sort VARCHAR(255),--sort fields and rules without adding an order by@IsGetCount bit  --Whether to get the total number of records, 1 to get the total number of records, 0 to not get the total number of records, return record set) asDeclare @strSql nvarchar(Max)SetNocount on;if(@IsGetCount = 1)begin Set @strSql='SELECT COUNT (0) from' + @tab + 'WHERE' + @strWhereEndElsebegin  Set @strSql='SELECT * FROM (select Row_number () over (ORDER by' + @Sort + ') as RowNum,' + @strFld + ' from' + @tab + 'where' + @strWhere + ') as Dwhere WHERE rownum between' + CAST(((@PageIndex-1)*@PageSize + 1) as nvarchar( -))+ ' and' + cast((@PageIndex*@PageSize) as nvarchar( -))Endexec(@strSql)SetNocountoff;

2, transmission pageindex*pagesize and pagesize

CREATE PROC [dbo].[Common_pagelist2](@tab nvarchar(Max),---table name@strFld nvarchar(Max),--Field String@strWhere varchar(Max),--Where Condition@StartIndex int,--number of start lines@PageSize int,--number of records to hold per page@Sort VARCHAR(255),--sort fields and rules without adding an order by@IsGetCount bit  --Whether to get the total number of records, 1 to get the total number of records, 0 to not get the total number of records, return record set) asDECLARE @strSql NVARCHAR(Max)SETNOCOUNT on;IF(@IsGetCount = 1)BEGIN SET @strSql='SELECT COUNT (0) from' + @tab + 'WHERE' + @strWhereENDELSEBEGIN  DECLARE @ENDINDEX INT  IF(@StartIndex>0)    BEGIN      SET @ENDINDEX=@StartIndex+@PageSize      SET @StartIndex=@StartIndex+1    END  ELSE    SET @ENDINDEX=@StartIndex+@PageSize         SET @strSql='SELECT * FROM (select Row_number () over (ORDER by' + @Sort + ') as RowNum,' + @strFld + ' from' + @tab + 'where' + @strWhere + ') as Dwhere WHERE rownum between' + CAST(@StartIndex  as VARCHAR( -))+ ' and' + CAST(@ENDINDEX  as VARCHAR( -))Endexec(@strSql)SETNOCOUNTOFF;

SQL SERVER Universal paging stored procedure, either of the two uses you choose