看了很多网上的分页存储过程，一般表名都是单个表，或者是单个视图。但经常会遇到多个表连接后的数据再分页，每次做视图太麻烦了，所以自己重新写了个，tablename可以是任意的select出的数据。
1，单个表
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[PageSingle]
(
@TableName NVARCHAR(200),--定义表名
@PK NVARCHAR(50),--定义主键
@FieldList NVARCHAR(800),--定义提取字段列
@RecordCount INT=0 OUTPUT,--输出记录总数
@PageCount INT=0 OUTPUT,--输出页面总数
@PageCurrent INT=1,--提取第几页记录
@PageSize INT=10,--每页记录数
@Where NVARCHAR(200)='',--查询条件，不带WHERE关键字
@SortFlag SMALLINT=1,--默认为降序，主键排序
@DoPage SMALLINT=3,--默认为单字段，首页非分页调用
@TableAlias NVARCHAR(20) = ''--多表链接别名
)
AS
BEGIN
DECLARE @vStart INT,@vSql NVARCHAR(4000),@vSort NVARCHAR(10),@vSortSub NVARCHAR(10),@vOperator NVARCHAR(4),@vOperatorSub NVARCHAR(4),@vFunction NVARCHAR(10),@vFunctionSub NVARCHAR(10)
IF (@Where IS NULL) OR (@Where='')
SET @Where = ''
ELSE
SET @Where = ' AND '+@Where
IF @SortFlag=1
BEGIN
SET @vSort = 'DESC'
SET @vSortSub = 'ASC'
SET @vOperator = '<='
SET @vOperatorSub = '>'
SET @vFunction = 'MIN'
SET @vFunctionSub = 'MAX'
END
ELSE
BEGIN
SET @vSort = 'ASC'
SET @vSortSub = 'DESC'
SET @vOperator = '>='
SET @vOperatorSub = '<'
SET @vFunction = 'MAX'
SET @vFunctionSub = 'MIN'
END
--处理多表别名或者视图
DECLARE @AliasFlag INT
SET @AliasFlag = 0
IF Len(@TableAlias)>0 AND @TableAlias!=''
SET @AliasFlag = 1
--对于首页进行特殊处理(单字段)
IF @DoPage=3
BEGIN
IF @AliasFlag=1
SET @vSql = 'SELECT TOP '+CAST(@pagesize AS NVARCHAR(10))+' '+@FieldList+' FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSort
ELSE
SET @vSql = 'SELECT TOP '+CAST(@pagesize AS NVARCHAR(10))+' '+@FieldList+' FROM '+@TableName+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSort
EXEC(@vSql)
RETURN
END
--统计记录总数
IF @AliasFlag=1
SET @vSql = 'SELECT @RecordCount=COUNT(*) FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0'+@Where
ELSE
SET @vSql = 'SELECT @RecordCount=COUNT(*) FROM '+@TableName+' WHERE 1>0'+@Where
EXEC sp_executesql @vSql,N'@RecordCount int output',@RecordCount OUTPUT
SET @PageCount = (@RecordCount+@PageSize-1)/@PageSize
IF @PageCurrent<=@PageCount/2--索取记录在前半部分
BEGIN
SET @vStart = (@PageCurrent-1)*@PageSize+1
IF @vStart<=1
BEGIN
IF @AliasFlag=1
SET @vSql = 'SELECT TOP '+CAST(@pagesize AS NVARCHAR(10))+' '+@FieldList+' FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSort
ELSE
SET @vSql = 'SELECT TOP '+CAST(@pagesize AS NVARCHAR(10))+' '+@FieldList+' FROM '+@TableName+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSort
END
ELSE
BEGIN
IF @AliasFlag=1
SET @vSql = 'SELECT TOP '+CAST(@pagesize AS nvarchar(10))+' '+@FieldList+' FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0 AND '+@PK+@vOperator+'(SELECT '+@vFunction+'('+@PK+') FROM (SELECT TOP '+CAST(@vStart AS nvarchar(10))+' '+@PK+' FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSort+') AS TempA)'+@Where+' ORDER BY '+@PK+' '+@vSort
ELSE
SET @vSql = 'SELECT TOP '+CAST(@pagesize AS nvarchar(10))+' '+@FieldList+' FROM '+@TableName+' WHERE 1>0 AND '+@PK+@vOperator+'(SELECT '+@vFunction+'('+@PK+') FROM (SELECT TOP '+CAST(@vStart AS nvarchar(10))+' '+@PK+' FROM '+@TableName+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSort+') AS TempA)'+@Where+' ORDER BY '+@PK+' '+@vSort
END
END
ELSE--要索取记录在后半部分
BEGIN
IF @PageCurrent>=@PageCount--如果当前页数超出总页数范围
BEGIN
SET @vStart=@RecordCount%@PageSize
IF @vStart=0
IF @AliasFlag=1
SET @vSql = 'SELECT * FROM (SELECT TOP '+CAST(@PageSize AS nvarchar(10))+' '+@FieldList+' FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSortSub+') AS tempa ORDER BY '+@PK+' '+@vSort
ELSE
SET @vSql = 'SELECT * FROM (SELECT TOP '+CAST(@PageSize AS nvarchar(10))+' '+@FieldList+' FROM '+@TableName+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSortSub+') AS tempa ORDER BY '+@PK+' '+@vSort
ELSE
IF @AliasFlag=1
SET @vSql = 'SELECT * FROM (SELECT TOP '+CAST(@vStart AS nvarchar(10))+' '+@FieldList+' FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSortSub+') AS tempa ORDER BY '+@PK+' '+@vSort
ELSE
SET @vSql = 'SELECT * FROM (SELECT TOP '+CAST(@vStart AS nvarchar(10))+' '+@FieldList+' FROM '+@TableName+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSortSub+') AS tempa ORDER BY '+@PK+' '+@vSort
END
ELSE
BEGIN
SET @vStart = @RecordCount-@PageCurrent*@PageSize
IF @AliasFlag=1
SET @vSql = 'SELECT * FROM (SELECT TOP '+CAST(@pagesize AS nvarchar(10))+' '+@FieldList+' FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0 AND '+@PK+@vOperatorSub+'(SELECT '+@vFunctionSub+'('+@PK+') FROM (SELECT TOP '+CAST(@vStart AS nvarchar(10))+' '+@PK+' FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSortSub+') AS tempa)'+@Where+' ORDER BY '+@PK+' '+@vSortSub+') AS tempb ORDER BY '+@PK+' '+@vSort
ELSE
SET @vSql = 'SELECT * FROM (SELECT TOP '+CAST(@pagesize AS nvarchar(10))+' '+@FieldList+' FROM '+@TableName+' WHERE 1>0 AND '+@PK+@vOperatorSub+'(SELECT '+@vFunctionSub+'('+@PK+') FROM (SELECT TOP '+CAST(@vStart AS nvarchar(10))+' '+@PK+' FROM '+@TableName+' WHERE 1>0'+@Where+' ORDER BY '+@PK+' '+@vSortSub+') AS tempa)'+@Where+' ORDER BY '+@PK+' '+@vSortSub+') AS tempb ORDER BY '+@PK+' '+@vSort
END
END
--SELECT(@vSql)
EXEC SP_EXECUTESQL @vSql
END
2，多个表连接的
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PageMultiple]
(
@TableName NVARCHAR(3000),--定义表名
@PK NVARCHAR(50),--定义主键
@FieldList NVARCHAR(800),--定义提取字段列
@RecordCount INT=0 OUTPUT,--输出记录总数
@PageCount INT=0 OUTPUT,--输出页面总数
@PageCurrent INT=1,--提取第几页记录
@PageSize INT=10,--每页记录数
@Where NVARCHAR(200)='',--查询条件，不带WHERE关键字
@SortList NVARCHAR(200)='',--默认主键降序,
@Version SMALLINT=2000,--默认数据库为2000，可以设置为2005
@DoCount SMALLINT = 0,--默认不统计总数
@TableAlias NVARCHAR(20) = ''--如果不用视图而用连接表，此处为连接表别名
)
AS
BEGIN
IF @DoCount=2--如果为0则不统计，为1则进行数量统计，如果为2则调用主键进行排序
BEGIN
EXEC PageSingle @TableName,@PK,@FieldList,@RecordCount OUTPUT,@PageCount OUTPUT,@PageCurrent,@PageSize,@Where,1,2,@TableAlias
RETURN
END
IF @DoCount=3--如果为0则不统计，为1则进行数量统计，如果为2则调用主键进行排序,如果为3则调用主键进行排序且不分页
BEGIN
EXEC PageSingle @TableName,@PK,@FieldList,@RecordCount OUTPUT,@PageCount OUTPUT,@PageCurrent,@PageSize,@Where,1,3,@TableAlias
RETURN
END
DECLARE @vStart INT,@vEnd INT,@vSql NVARCHAR(4000),@vTop INT
IF (@Where IS NULL) OR (@Where='')
SET @Where = ''
ELSE
SET @Where = ' AND '+@Where
IF (@SortList IS NULL) OR (@SortList='')
SET @SortList = ' ORDER BY '+@PK+' DESC '
ELSE
SET @SortList = ' ORDER BY '+@SortList+' '
--处理链接表别名问题
DECLARE @AliasFlag INT
SET @AliasFlag = 0
IF Len(@TableAlias)>0 and @TableAlias!=' '
SET @AliasFlag = 1
--对于首页调用进行特殊处理(多字段)
IF @DoCount=-1--如果为-1则为多字段首页调用且不分页，如果为0则不统计，为1则进行数量统计，如果为2则调用主键进行排序,如果为3则调用主键进行排序且不分页
BEGIN
IF @AliasFlag = 1
SET @vSql = 'SELECT TOP '+CAST(@PageSize AS NVARCHAR(10))+' '+@FieldList+' FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0 '+@Where+' '+@SortList+' '
ELSE
SET @vSql = 'SELECT TOP '+CAST(@PageSize AS NVARCHAR(10))+' '+@FieldList+' FROM '+@TableName+' WHERE 1>0 '+@Where+' '+@SortList+' '
EXEC(@vSql)
RETURN
END
IF @DoCount = 1
BEGIN
IF @AliasFlag = 1
SET @vSql = 'SELECT @RecordCount=COUNT(*) FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0 '+@Where+' '
ELSE
SET @vSql = 'SELECT @RecordCount=COUNT(*) FROM '+@TableName+' WHERE 1>0 '+@Where+' '
--统计记录总数
EXEC SP_EXECUTESQL @vSql,N'@RecordCount INT OUTPUT ',@RecordCount OUTPUT
--统计分页总数
SET @PageCount = (@RecordCount+@PageSize-1)/@PageSize
END
--确定起始位置
SET @vStart = (@PageCurrent-1)*@PageSize+1
--确定结束位置
SET @vEnd = @vStart+@PageSize-1
--只提取包含目标记录的最小数量
SET @vTop = @PageCurrent*@PageSize
IF @Version=2000--如果数据库为2000
BEGIN
DECLARE @s NVARCHAR(10),@e NVARCHAR(10),@top NVARCHAR(10)
SET @s = CAST(@vStart AS NVARCHAR(10))
SET @e = CAST(@vEnd AS NVARCHAR(10))
SET @top = CAST(@vTop AS NVARCHAR(10))
SET @vSql =' DECLARE @Table TABLE(id BIGINT IDENTITY(1,1),oldid BIGINT)'
SET @vSql = @vSql + ' DECLARE @sStart NVARCHAR(10),@sEnd NVARCHAR(10) '
SET @vSql = @vSql + ' SET @sStart = '+@s
SET @vSql = @vSql + ' SET @sEnd = '+@e
SET @vSql = @vSql + ' SET NOCOUNT ON'
SET @vSql = @vSql + ' SET ROWCOUNT '+@top
--判断是否使用了表别名
IF @AliasFlag = 1
SET @vSql = @vSql + ' INSERT INTO @Table(oldid) SELECT '+@PK+' FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0 '+@Where+' '+@SortList
ELSE
SET @vSql = @vSql + ' INSERT INTO @Table(oldid) SELECT '+@PK+' FROM '+@TableName+' WHERE 1>0 '+@Where+' '+@SortList
SET @vSql = @vSql + ' SET NOCOUNT OFF'
--判断是否使用了表别名
IF @AliasFlag=1
SET @vSql = @vSql + ' SELECT '+@FieldList+' FROM @Table as Temp_aaa INNER JOIN ('+@TableName+') AS '+@TableAlias+' ON Temp_aaa.oldid='+@TableAlias+'.'+@PK+' WHERE Temp_aaa.id BETWEEN @sStart AND @sEnd ORDER BY Temp_aaa.id'
ELSE
SET @vSql = @vSql + ' SELECT Temp_bbb.'+@FieldList+' FROM @Table as Temp_aaa INNER JOIN '+@TableName+' AS Temp_bbb ON Temp_aaa.oldid=Temp_bbb.'+@PK+' WHERE Temp_aaa.id BETWEEN @sStart AND @sEnd ORDER BY Temp_aaa.id'
SET @vSql = @vSql + ' SET ROWCOUNT 0'
--SELECT @vSql
EXEC (@vSql)
END
ELSE--如果数据库为2005
BEGIN
DECLARE @sStart NVARCHAR(10),@sEnd NVARCHAR(10),@sTop NVARCHAR(10)
SET @sStart = CAST(@vStart AS NVARCHAR(10))
SET @sEnd = CAST(@vEnd AS NVARCHAR(10))
--提取包含检索记录的最小记录数量
SET @sTop = CAST(@vTop AS NVARCHAR(10))
SET @vSql = @vSql + ' SET ROWCOUNT '+ @sTop
IF @AliasFlag = 1
BEGIN
--SET @vSql = ' WITH TempABC AS(SELECT ROW_NUMBER() OVER('+@SortList+') AS ROW,'+@FieldList+' FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0 '+@Where+')'
--SET @vSql = @vSql + ' SELECT '+@FieldList+' FROM TempABC WHERE ROW BETWEEN '+@sStart+' AND '+@sEnd
SET @vSql = ' WITH TempABC AS(SELECT ROW_NUMBER() OVER('+@SortList+') AS ROW,'+@TableAlias+'.'+@PK+' AS TempNewPK FROM ('+@TableName+') AS '+@TableAlias+' WHERE 1>0 '+@Where+')'
SET @vSql = @vSql + ' SELECT '+@TableAlias+'.'+@FieldList+' FROM TempABC INNER JOIN ('+@TableName+') AS '+@TableAlias+' ON TempABC.TempNewPK='+@TableAlias+'.'+@PK+' WHERE TempABC.ROW BETWEEN '+@sStart+' AND '+@sEnd
END
ELSE
BEGIN
--SET @vSql = ' WITH TempABC AS(SELECT ROW_NUMBER() OVER('+@SortList+') AS ROW,Temp_O.'+@FieldList+' FROM '+@TableName+' AS Temp_O WHERE 1>0 '+@Where+')'
--SET @vSql = @vSql + ' SELECT '+@FieldList+' FROM TempABC WHERE ROW BETWEEN '+@sStart+' AND '+@sEnd
SET @vSql = ' WITH TempABC AS(SELECT ROW_NUMBER() OVER('+@SortList+') AS ROW,Temp_O.'+@PK+' AS TempNewPK FROM '+@TableName+' AS Temp_O WHERE 1>0 '+@Where+')'
SET @vSql = @vSql + ' SELECT Temp_O.'+@FieldList+' FROM TempABC INNER JOIN '+@TableName+' AS Temp_O ON TempABC.TempNewPK=Temp_O.'+@PK+' WHERE ROW BETWEEN '+@sStart+' AND '+@sEnd
END
SET @vSql = @vSql + ' ORDER BY TempABC.ROW SET ROWCOUNT 0'
--SELECT @vSql
EXEC SP_EXECUTESQL @vSql
END
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
有什么需要改进的地方，还望大家多多指教。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
