分页查询
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetPaginationDataByKey]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetPaginationDataByKey]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*
通用分页查询存储过程(按唯一键分页)
用法:
exec GetPaginationDataByKey @nvcharTable,@nvcharKeyField,@nvcharWhere,@nvcharSort,
@bitNoPage,@intPageSize,@intPageNo,@bitReturnAllOnFail,@intTotalCount output
DECLARE @intTotalCountReturn int
exec GetPaginationDataByKey 'ForumTopic','ID','',' ReplyCount DESC',
0,5,2,0,@intTotalCount = @intTotalCountReturn output
PRINT @intTotalCountReturn
*/
CREATE PROCEDURE dbo.GetPaginationDataByKey
(
@nvcharTable nvarchar(500), --要查询的表名或视图名称
@nvcharKeyField nvarchar(200), --表或视图中唯一标识记录的字段名称
@nvcharWhere nvarchar(2000), --Where子句
@nvcharSort nvarchar(2000), --ORDER BY子句
@bitNoPage bit, --是否需要分页返回 0:是 1:否
@intPageSize int, --每页数量
@intPageNo int, --当前页码
@bitReturnAllOnFail bit, --当参数不合法时,是否所有数据 1:是 0:否
@intTotalCount int output --所有符合条件的记录数量
)
AS
SET XACT_ABORT ON
SET NOCOUNT ON
--修订表名
DECLARE @nvcharTableNew nvarchar(500)
IF( @nvcharTable <> '')
BEGIN
SET @nvcharTableNew = ' [' + @nvcharTable + ']'
END
ELSE
BEGIN
SET @nvcharTableNew = ''
END
--修订Where语句
DECLARE @nvcharWhereNew nvarchar(2000)
IF(@nvcharWhere<> '')
SET @nvcharWhereNew = ' WHERE ( ' + @nvcharWhere + ' ) '
ELSE
SET @nvcharWhereNew = ' WHERE 1 = 1 '
--修订排序语句
DECLARE @nvcharSortNew nvarchar(2000)
IF(@nvcharSort <> '')
BEGIN
SET @nvcharSortNew = ' ORDER BY ' + @nvcharSort
END
ELSE
BEGIN
SET @nvcharSortNew = ''
END
--修订关键字段
DECLARE @nvcharKeyFieldNew nvarchar(200)
IF(@nvcharKeyField <> '')
BEGIN
SET @nvcharKeyFieldNew = '[' + @nvcharKeyField + ']'
END
ELSE
BEGIN
SET @nvcharKeyFieldNew = ''
END
--记录总数
DECLARE @QueryCountString NVARCHAR(4000)
SET @QueryCountString = 'SELECT @intTotal = COUNT(*) FROM ' + @nvcharTableNew + @nvcharWhereNew
execute sp_executesql @QueryCountString,
N'@nvcharTableNew nvarchar(2000), @nvcharWhereNew nvarchar(2000),@intTotal int output',
@nvcharTableNew, @nvcharWhereNew,@intTotalCount output
--如果不分页时返回所有
IF(@bitNoPage = 1)
BEGIN
EXEC('SELECT * FROM ' + @nvcharTableNew + @nvcharWhereNew + @nvcharSortNew)
RETURN
END
--参数不合法按要求返回空数据集或所有数据
IF(@intPageSize <= 0 OR @intPageNo < 1 OR @nvcharSortNew = '' OR @nvcharKeyFieldNew = '')
BEGIN
IF(@bitReturnAllOnFail = 0)
BEGIN
EXEC('SELECT Top 0 * FROM ' + @nvcharTableNew)
END
ELSE
BEGIN
EXEC('SELECT * FROM ' + @nvcharTableNew + @nvcharWhereNew + @nvcharSortNew)
END
RETURN
END
--查询语句
DECLARE @nvcharSql nvarchar(4000)
SET @nvcharSql =
' SELECT Top ' + CONVERT(nvarchar(200),@intPageSize) + ' * FROM ' + @nvcharTableNew + @nvcharWhereNew
+ ' AND '
+ @nvcharKeyFieldNew + ' NOT IN ('
+ ' SELECT Top ' + CONVERT(nvarchar(200),@intPageSize * (@intPageNo - 1)) + ' ' + @nvcharKeyFieldNew + ' FROM ' + @nvcharTableNew + @nvcharWhereNew + @nvcharSortNew
+ ' ) '
+ @nvcharSortNew
execute sp_executesql @nvcharSql,
N'@intPageSize int,@nvcharTableNew nvarchar(500),@nvcharWhereNew nvarchar(2000), @nvcharKeyFieldNew nvarchar(200),@intPageNo int,@nvcharSortNew nvarchar(2000)',
@intPageSize,@nvcharTableNew,@nvcharWhereNew,@nvcharKeyFieldNew,@intPageNo,@nvcharSortNew
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop procedure [dbo].[GetPaginationDataByKey]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*
通用分页查询存储过程(按唯一键分页)
用法:
exec GetPaginationDataByKey @nvcharTable,@nvcharKeyField,@nvcharWhere,@nvcharSort,
@bitNoPage,@intPageSize,@intPageNo,@bitReturnAllOnFail,@intTotalCount output
DECLARE @intTotalCountReturn int
exec GetPaginationDataByKey 'ForumTopic','ID','',' ReplyCount DESC',
0,5,2,0,@intTotalCount = @intTotalCountReturn output
PRINT @intTotalCountReturn
*/
CREATE PROCEDURE dbo.GetPaginationDataByKey
(
@nvcharTable nvarchar(500), --要查询的表名或视图名称
@nvcharKeyField nvarchar(200), --表或视图中唯一标识记录的字段名称
@nvcharWhere nvarchar(2000), --Where子句
@nvcharSort nvarchar(2000), --ORDER BY子句
@bitNoPage bit, --是否需要分页返回 0:是 1:否
@intPageSize int, --每页数量
@intPageNo int, --当前页码
@bitReturnAllOnFail bit, --当参数不合法时,是否所有数据 1:是 0:否
@intTotalCount int output --所有符合条件的记录数量
)
AS
SET XACT_ABORT ON
SET NOCOUNT ON
--修订表名
DECLARE @nvcharTableNew nvarchar(500)
IF( @nvcharTable <> '')
BEGIN
SET @nvcharTableNew = ' [' + @nvcharTable + ']'
END
ELSE
BEGIN
SET @nvcharTableNew = ''
END
--修订Where语句
DECLARE @nvcharWhereNew nvarchar(2000)
IF(@nvcharWhere<> '')
SET @nvcharWhereNew = ' WHERE ( ' + @nvcharWhere + ' ) '
ELSE
SET @nvcharWhereNew = ' WHERE 1 = 1 '
--修订排序语句
DECLARE @nvcharSortNew nvarchar(2000)
IF(@nvcharSort <> '')
BEGIN
SET @nvcharSortNew = ' ORDER BY ' + @nvcharSort
END
ELSE
BEGIN
SET @nvcharSortNew = ''
END
--修订关键字段
DECLARE @nvcharKeyFieldNew nvarchar(200)
IF(@nvcharKeyField <> '')
BEGIN
SET @nvcharKeyFieldNew = '[' + @nvcharKeyField + ']'
END
ELSE
BEGIN
SET @nvcharKeyFieldNew = ''
END
--记录总数
DECLARE @QueryCountString NVARCHAR(4000)
SET @QueryCountString = 'SELECT @intTotal = COUNT(*) FROM ' + @nvcharTableNew + @nvcharWhereNew
execute sp_executesql @QueryCountString,
N'@nvcharTableNew nvarchar(2000), @nvcharWhereNew nvarchar(2000),@intTotal int output',
@nvcharTableNew, @nvcharWhereNew,@intTotalCount output
--如果不分页时返回所有
IF(@bitNoPage = 1)
BEGIN
EXEC('SELECT * FROM ' + @nvcharTableNew + @nvcharWhereNew + @nvcharSortNew)
RETURN
END
--参数不合法按要求返回空数据集或所有数据
IF(@intPageSize <= 0 OR @intPageNo < 1 OR @nvcharSortNew = '' OR @nvcharKeyFieldNew = '')
BEGIN
IF(@bitReturnAllOnFail = 0)
BEGIN
EXEC('SELECT Top 0 * FROM ' + @nvcharTableNew)
END
ELSE
BEGIN
EXEC('SELECT * FROM ' + @nvcharTableNew + @nvcharWhereNew + @nvcharSortNew)
END
RETURN
END
--查询语句
DECLARE @nvcharSql nvarchar(4000)
SET @nvcharSql =
' SELECT Top ' + CONVERT(nvarchar(200),@intPageSize) + ' * FROM ' + @nvcharTableNew + @nvcharWhereNew
+ ' AND '
+ @nvcharKeyFieldNew + ' NOT IN ('
+ ' SELECT Top ' + CONVERT(nvarchar(200),@intPageSize * (@intPageNo - 1)) + ' ' + @nvcharKeyFieldNew + ' FROM ' + @nvcharTableNew + @nvcharWhereNew + @nvcharSortNew
+ ' ) '
+ @nvcharSortNew
execute sp_executesql @nvcharSql,
N'@intPageSize int,@nvcharTableNew nvarchar(500),@nvcharWhereNew nvarchar(2000), @nvcharKeyFieldNew nvarchar(200),@intPageNo int,@nvcharSortNew nvarchar(2000)',
@intPageSize,@nvcharTableNew,@nvcharWhereNew,@nvcharKeyFieldNew,@intPageNo,@nvcharSortNew
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--SQL 2005
WITH TempQuery AS
(
SELECT {0}, ROW_NUMBER() OVER (ORDER BY {3}) AS 'RowNumberForSplit'
FROM {1}
WHERE 1 = 1 {2}
)
SELECT *
FROM TempQuery
WHERE RowNumberForSplit BETWEEN {4} AND {5};
WITH TempQuery AS
(
SELECT {0}, ROW_NUMBER() OVER (ORDER BY {3}) AS 'RowNumberForSplit'
FROM {1}
WHERE 1 = 1 {2}
)
SELECT *
FROM TempQuery
WHERE RowNumberForSplit BETWEEN {4} AND {5};