SQL SERVER Universal Paging stored procedures

SQL SERVER Universal Paging stored procedures

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 @pagesize int,--Number of records per page @sort VARCHAR (255),--sort fields and rules without 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 recordsets asdeclare @strSql nvarchar (max) set NOCOUNT on;if (@IsGetCount = 1) begin set @strSql = ' SELECT COUNT (0) from ' + @tab + ' wher E ' + @strWhereendelsebegin  Set @strSql = ' SELECT * FROM (select Row_number () over   (ORDER by ' + @Sort + ') as Rownu M, ' + @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) set NOCOUNT off;

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,-- Start line number @pagesize int,--Number of records per page @sort VARCHAR (255),--sort fields and rules without 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 recordset) Asdeclare @strSql NVARCHAR (max) set NOCOUNT on;if (@IsGetCount = 1) BEGIN Set @strSql = ' SELECT COUNT (0) from  ' + @tab + ' WHERE ' + @strWhereENDELSEBEGIN  DECLARE @ENDINDEX INT  IF (@StartIndex >0)    BEGIN      SET @[email Protected][email protected]      set @[email protected]+1    END  ELSE    set @[email protected][email protected  ]         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) SET NOCOUNT OFF;

SQL SERVER Universal paging stored procedures