存储过程:
CREATE PROCEDURE [dbo].[ShowPage]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@strOrder varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@strWhere varchar(1500) = '', -- 查询条件 (注意: 不要加 where)
@TotalCount INT OUTPUT, --返回记录总数
@TotalPageCount INT OUTPUT --返回总页数
AS
declare @strSQL nvarchar(4000)
declare @SqlCount nvarchar(4000)
SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+ CAST(@PageSize AS VARCHAR)+') FROM ' + @tblName + ' where '+ @strWhere
EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
@TotalCount OUTPUT,@TotalPageCount OUTPUT
if @strWhere !=''
set @strWhere=' where '+@strWhere
set @strSQL='Select * FROM (Select ROW_NUMBER() OVER ('+@strOrder+') AS pos,'+@strGetFields+' FROM ['+@tblName+']'+@strWhere+') AS sp Where pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
exec (@strSQL)