sp_util_page.sql

asp分类支持多级

if exists (select * from dbo.sysobjects where id = object_id(N'[sp_Util_Page]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [sp_Util_Page]
GO

/*
分页
调用方法:exec sp_Util_Page 440000,4,10,'MID','MID,ip1,ip2,country,city','tbTempPage','1=1','mid asc'
输入:
	1.记录条数(已有值:外部赋值,0执行count)
	2.当前页数
	3.每页记录数
	4.主键(一定要有)
	5.字段
	6.表名
	7.条件(不需要where)
	8.排序(不需要order by,需要asc和desc字符)
返回:记录集
*/
CREATE PROCEDURE sp_Util_Page
(
	@iRecordCount float OUTPUT,
	@iPageCurr int,
	@iPageSize int,
	@sPkey nvarchar(50),
	@sField nvarchar(1000),
	@sTable nvarchar(100),
	@sCondition nvarchar(1000),
	@sOrder nvarchar(100)
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @sC1 nvarchar(1000),@sC2 nvarchar(1000)
	DECLARE @iAsc int,@iDesc int,@iType tinyint
	DECLARE @sT1 nvarchar(1000),@sT2 nvarchar(1000),@sT3 nvarchar(1000),@sT4 nvarchar(1000),@sSQL nvarchar(4000)

	SELECT @sC1='', @sC2=' WHERE '
	IF LEN(@sCondition)>2
		BEGIN
			SELECT @sC1=' WHERE '+@sCondition+' ', @sC2=' WHERE '+@sCondition+' AND '
		END

	SELECT @sT1=UPPER(@sOrder), @sT2=@sOrder, @iType=0, @sOrder='', @sT4=UPPER(@sPkey)
	IF LEN(@sT2)>2
		BEGIN
			SELECT @iAsc=0, @iDesc=0
			IF @sT4=SUBSTRING(@sT1,0,LEN(@sT4)) --存在主建
				BEGIN
					SELECT @iAsc=CHARINDEX('ASC',@sT1), @iDesc=CHARINDEX('DESC',@sT1)
				END

			IF (@iAsc>0 and @iDesc=0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc<@iDesc))
				SELECT @iType=1, @sT3='>(SELECT MAX('
			ELSE IF (@iAsc=0 and @iDesc>0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc>@iDesc))
				BEGIN
					SELECT @iType=1, @sT3='<(SELECT MIN('
				END
			SET @sOrder=' ORDER BY '+@sT2
		END

	IF @iRecordCount<1
		BEGIN
			SET @sSQL='SELECT @iRecordCount=Count(0) FROM '+@sTable+@sC1
			EXEC sp_executesql @sSQL,N'@iRecordCount float OUT',@iRecordCount OUT
		END

	SELECT @iPageCurr=(CASE WHEN @iRecordCount<(@iPageCurr-1)*@iPageSize THEN CEILING(@iRecordCount/@iPageSize) WHEN @iPageCurr<1 THEN 1 ELSE @iPageCurr END)

	IF (@iPageCurr>1) AND (@iType=1)
		SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC2+@sPkey+@sT3+@sPkey+') FROM (SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+') AS tbTemp)'+@sOrder
	ELSE IF (@iPageCurr>1) AND (@iType=0)
		SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sC2+@sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC2+@sPkey+' NOT IN(SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+')'+@sOrder+')'+@sOrder
	ELSE
		BEGIN
			SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC1+@sOrder
		END

EXEC(@sSQL)

END
GO