存储过程
ALTER PROCEDURE [dbo].[P_GridViewPager] (
@select VARCHAR(1000) = '*', --查询字段
@table VARCHAR(800), --表名
@where VARCHAR(800) = '', --WHERE条件
@orderField VARCHAR(200), --排序条件
@pageSize INT = 20, --每页记录数
@pageNum INT =1, --当前页
@KeyField VARCHAR(200) = 'Id', --索引字段
@recordTotal INT OUTPUT --输出记录总数
)
AS
BEGIN
DECLARE @beginRow INT
DECLARE @endRow INT
DECLARE @tempLimit VARCHAR(200)
DECLARE @tempCount NVARCHAR(1000)
DECLARE @tempMain VARCHAR(1000)
DECLARE @tempMain2 VARCHAR(1000)
--declare @timediff datetime
set nocount on
--select @timediff=getdate() --记录时间
SET @beginRow = (@pageNum - 1) * @pageSize + 1
SET @endRow = @pageNum * @pageSize
SET @tempLimit = 'rows BETWEEN ' + CAST(@beginRow AS VARCHAR) +' AND '+CAST(@endRow AS VARCHAR)
if @pageNum = 1
begin
--if @where != ''
set @tempMain = 'select top ' + str(@pageSize) +' '+@select+ ' from ' + @table + ' where ' + @where + ' order by ' + @orderField
--else
--set @tempMain = 'select top ' + str(@pageSize) +' '+@select+ ' from '+ @table + ' order by '+ @orderField
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
--主查询返回结果集
SET @tempMain = 'SELECT '+@select+' FROM (SELECT ROW_NUMBER() OVER (order by '+@orderField+') AS rows ,'+@select+' FROM '+@table+' WHERE '+@where+') AS main_temp WHERE '+@tempLimit
--SET @tempMain = 'SELECT '+@select+',rows FROM (SELECT ROW_NUMBER() OVER (order by '+@orderField+') AS rows ,'+@select+' FROM '+@table+' WHERE '+@where+') as tempView where tempView.rows in(SELECT rows FROM (SELECT ROW_NUMBER() OVER (order by '+@orderField+') AS rows FROM '+@table+' WHERE '+@where+') AS main_temp WHERE '+@tempLimit+') order by rows'
end
PRINT @tempMain
EXECUTE (@tempMain)
-- 查询总数语句
--输出参数为总记录数--'SELECT COUNT(*) FROM (SELECT '+@orderField+' FROM '+@table+' WHERE '+@where+') AS my_temp'
SET @tempCount = 'select count(1) as Count from ' + @table + ' where '+ @where
EXECUTE sp_executesql @tempCount,N'@recordTotal INT OUTPUT',@recordTotal OUTPUT
--select datediff(ms,@timediff,getdate()) as 耗时
set nocount off
END