asp ´æ´¢¹ý³Ì£¨·ÖÒ³£©

CREATE PROCEDURE sp_newpage
@tb varchar(50), --±íÃû
@col varchar(50), --°´¸ÃÁÐÀ´½øÐзÖÒ³
@coltype int, --@colÁеÄÀàÐÍ,0-Êý×ÖÀàÐÍ,1-×Ö·ûÀàÐÍ,2-ÈÕÆÚʱ¼äÀàÐÍ
@orderby bit, --ÅÅÐò,0-˳Ðò,1-µ¹Ðò
@collist varchar(800),--Òª²éѯ³öµÄ×Ö¶ÎÁбí,*±íʾȫ²¿×Ö¶Î
@pagesize int, --ÿҳ¼Ç¼Êý
@page int, --Ö¸¶¨Ò³
@condition varchar(800),--²éѯÌõ¼þ
@RecordCount Int Output,
@pages int OUTPUT --×ÜÒ³Êý
AS
/*
¹¦ÄÜÃèÊö£º¶ÔÖ¸¶¨±íÖÐÂú×ãÌõ¼þµÄ¼Ç¼°´Ö¸¶¨ÁнøÐзÖÒ³²éѯ£¬·ÖÒ³¿ÉÒÔ˳Ðò¡¢µ¹Ðò
²éѯ¿ÉÒÔÖ¸¶¨Ò³´óС¡¢Ö¸¶¨²éѯÈÎÒâÒ³¡¢Ö¸¶¨Êä³ö×Ö¶ÎÁÐ±í£¬·µ»Ø×ÜÒ³Êý
×÷ Õߣºpbsql
°æ ±¾£º1.10
×îºóÐ޸ģº2004-11-29
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--ûÓвéѯÌõ¼þ
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--ÓвéѯÌõ¼þ
SET @where1=' WHERE ('+@condition+') AND '--±¾À´ÓÐÌõ¼þÔÙ¼ÓÉÏ´ËÌõ¼þ
SET @where2=' WHERE ('+@condition+') '--Ô­±¾Ã»ÓÐÌõ¼þ¶ø¼ÓÉÏ´ËÌõ¼þ
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
') from '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--¼ÆËã×ÜÒ³Êý

SET @sql='SELECT @RecordCount=COUNT(*) from '+@tb+@where2
EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT--¼ÆËã×ܼǼ

IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' from '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' from (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' from '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '