110,567
社区成员
发帖
与我相关
我的任务
分享
USE [NDKM]
GO
/****** 对象: StoredProcedure [dbo].[doc_PagingPR] 脚本日期: 04/09/2008 08:59:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[doc_PagingPR]
(
---- 输入参数 ----
@PageSize INT, ---- 页面大小
@CurrentPageIndex INT, ---- 当前页索引
@DataTableCode VARCHAR(50), ---- 数据表代码
@DataKeyCode VARCHAR(50), ---- 数据主键代码
@DESCFlag BIT = 0, ---- 按逆序输出还是正序输出
@SearchCondition VARCHAR(1000) = '' ---- 检索条件
)
AS
---- 关闭计数器 ----
SET NOCOUNT ON
---- 变量声明 ----
DECLARE @CurrentID INT
DECLARE @strSQL NVARCHAR(500)
DECLARE @OrderBy NVARCHAR(50)
DECLARE @OffsetNum INT
---- 检索条件处理 ----
IF @SearchCondition <> ''
SET @SearchCondition = N' WHERE ' + @SearchCondition
---- 排序条件处理 ----
IF @DESCFlag = 0
SET @OrderBy = N' Order By ' + @DataKeyCode + ' ASC '
ELSE
SET @OrderBy = N' Order By ' + @DataKeyCode + ' DESC '
---- 当前页索引处理 ----
IF @CurrentPageIndex < 0
SET @CurrentPageIndex = 0
---- 计算当前页第一条记录偏移量 ----
SELECT @OffsetNum = @CurrentPageIndex * @PageSize + 1
---- 读取当前页第一条记录的唯一标识 ----
SET @strSQL = N' SET ROWCOUNT @OffsetNum '
SET @strSQL = @strSQL + N' SELECT @CurrentID = ' + @DataKeyCode + ' FROM ' + @DataTableCode + @SearchCondition + ' ' + @OrderBy
SET @strSQL = @strSQL + N' SET ROWCOUNT 0 '
EXEC SP_EXECUTESQL @strSQL, N'@OffsetNum INT,@DataKeyCode VARCHAR(50), @DataTableCode VARCHAR(50), @SearchCondition VARCHAR(50), @OrderBy VARCHAR(20), @CurrentID INT OUTPUT', @OffsetNum,@DataKeyCode, @DataTableCode, @SearchCondition, @OrderBy, @CurrentID OUTPUT
---- 条件处理 ----
IF @SearchCondition = ''
IF @DESCFlag = 0
SET @SearchCondition = ' WHERE ' + @DataKeyCode + ' >= ' + str(@CurrentID)
ELSE
SET @SearchCondition = ' WHERE ' + @DataKeyCode + ' <= ' + str(@CurrentID)
ELSE
IF @DESCFlag = 0
SET @SearchCondition = @SearchCondition + ' AND ' + @DataKeyCode + ' >= ' + str(@CurrentID)
ELSE
SET @SearchCondition = @SearchCondition + ' AND ' + @DataKeyCode + ' <= ' + str(@CurrentID)
---- 获取最终结果 ----
SET @strSQL = N'SELECT Top ' + RTRIM(LTRIM(STR(@PageSize))) + ' * FROM ' + @DataTableCode + @SearchCondition + ' ' + @OrderBy
EXEC SP_EXECUTESQL @strSQL, N'@PageSize INT,@DataKeyCode VARCHAR(50), @DataTableCode VARCHAR(50), @SearchCondition VARCHAR(50), @OrderBy VARCHAR(20)', @OffsetNum,@DataKeyCode, @DataTableCode, @SearchCondition, @OrderBy
---- 打开计数器 ----
SET NOCOUNT ON
RETURN
select top @maxlen from 表名 where 主键字段名 not in (select top @startindex 主键字段名 from 表名 order by 排序字段名)