pagesingle.txt

自己写的二分分页脚本

CREATE 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