 这就是我 |
|
|
| 用户登陆 |
|
|
| 最新公告 |
数据载入中，请稍候...... |
| 站点日历 |
数据载入中，请稍候...... |
| 最新日志 |
数据载入中，请稍候...... |
| 最新回复 |
|
| 最新留言 |
数据载入中，请稍候...... |
 日志搜索 |
|
数据载入中，请稍候...... |
|
 友情链接 |
|
 其他信息 |
|
|
|
|
|
|
|
|
|
| ·叶子的分页存储过程 -|cloudy 发表于 2006-6-28 11:06:13 |
/*
名称: 叶子分页存储过程
Name: ShowoPage(mssql sp)
RCSfile: sp_Util_Page.sql
Revision: 0.10
Author: Yehe(叶子)
Released: 2005-08-27 09:24:21
Description:mssql2k分页存储过程
Contact: QQ:311673,MSN:mmyehe@msn.com,GT:mmyehe@gmail.com
WebSite: http://www.yehe.org,http://www.showo.com
调用示范:
exec sp_Util_Page 1000000,3,10,'Thread_ID','Thread_ID,Thread_Title,Thread_UserName','Community_Thread','','Thread_ID asc'
输入:
记录条数,已有值(程序外部自写sql语句赋值),0执行count
当前页数
每页记录数
主键
字段
表
条件,不需要where
排序,不需要order by,需要asc和desc字符
返回:
返回记录集数组,如果父级ID不存在,则数组为空
*/
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
CREATE PROCEDURE sp_Util_Page
(
@iRecordCount INT OUTPUT,
@iPageCurr INT,
@iPageSize INT,
@sPkey NVARCHAR(50),
@sField NVARCHAR(1000),
@sTable NVARCHAR(100),
@sCondition NVARCHAR(1000),
@sOrder NVARCHAR(100)
)
AS
SET NOCOUNT ON
DECLARE @sCond1 NVARCHAR(1000)
DECLARE @sCond2 NVARCHAR(1000)
DECLARE @iAsc INT
DECLARE @iDesc INT
DECLARE @sTmp NVARCHAR(1000)
DECLARE @sOrderTmp NVARCHAR(1000)
DECLARE @sSQL NVARCHAR(4000)
IF LEN(@sCondition)>2
BEGIN
SET @sCond1=' WHERE '+@sCondition
SET @sCond2=' WHERE '+@sCondition+' AND '
END
ELSE
BEGIN
SET @sCond1=''
SET @sCond2=' WHERE '
END
IF LEN(@sOrder)<4
SET @sOrder=@sPkey+' DESC'
SET @sOrderTmp=UPPER(@sOrder)
SET @sOrderTmp=SUBSTRING(@sOrderTmp,CHARINDEX(UPPER(@sPkey),@sOrderTmp),LEN(@sOrderTmp))
SET @iAsc=CHARINDEX('ASC',@sOrderTmp)
SET @iDesc=CHARINDEX('DESC',@sOrderTmp)
IF (@iAsc>0 and @iDesc=0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc<@iDesc))
SET @sTmp='>(SELECT MAX('
ELSE IF (@iAsc=0 and @iDesc>0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc>@iDesc))
SET @sTmp='<(SELECT MIN('
ELSE
BEGIN
SET @sOrder=@sPkey+' DESC'
SET @sTmp='<(SELECT MIN('
END
SET @sOrder=' ORDER BY '+@sOrder
IF @iRecordCount<1
BEGIN
SET @sSQL='SELECT @iRecordCount=Count(0) FROM '+@sTable
EXEC sp_executesql @sSQL,N'@iRecordCount INT OUT',@iRecordCount OUT
END
IF @iRecordCount<(@iPageCurr-1)*@iPageSize
SET @iPageCurr=CEILING(@iRecordCount/@iPageSize)
ELSE IF @iPageCurr<1
SET @iPageCurr=1
IF @iPageCurr=1
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS NVARCHAR)+' '+@sField+' FROM '+@sTable+' '+@sCond1+' '+@sOrder
ELSE
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS NVARCHAR)+' '+@sField+' FROM '+@sTable+' '+@sCond2+' '+@sPkey+@sTmp+@sPkey+') FROM (SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS NVARCHAR)+' '+@sPkey+' FROM '+@sTable+' '+@sCond1+' '+@sOrder+') AS tbTemp) '+@sOrder
EXEC(@sSQL)
RETURN(@iRecordCount)
GO
|
| [阅读全文 | 回复(0) | 引用通告 | 编辑] |
|
|
|
|
|
| |
