/**********************************************************************************************
********************通过指定的条件分页查询数据表【TableName or ViewName】记录******************
**********************************************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[SjjPagination]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[SjjPagination]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--参数说明
-------------------------------------------------------------
/*
* @tblName ----要显示的表或多个表的连接
* @fldName ----要查询出的字段列表,*表示全部字段
* @pageSize ----每页显示的记录个数
* @pageIndex ----要显示那一页的记录
* @fldSort ----排序字段列表或条件,如:id desc (多个id desc,dt asc)
* @strCondition ----查询条件,不需where
* @pageCount ----查询结果分页后的总页数
* @RecordCount ----查询到的总记录数
* @UsedTime ----耗时测试时间差
* @strSql ----最后返回的SQL语句
*/
CREATE PROCEDURE [dbo].[SjjPagination]
(
@tblName NVARCHAR(MAX),
@fldName NVARCHAR(MAX),
@pageSize INT,
@pageIndex INT,
@fldSort NVARCHAR(MAX),
@strCondition NVARCHAR(MAX),
@pageCount INT OUTPUT,
@RecordCount INT OUTPUT,
@UsedTime INT OUTPUT,
@strSql nvarchar(max) = '' OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @startRow int, @endRow INT ----用于存放起始值与结束值
DECLARE @timediff DATETIME ----用于存放时间差值
DECLARE @SqlCounts NVARCHAR(MAX) ----用于存放总记录数查询语句
DECLARE @strTmp NVARCHAR(MAX) ----用于存放查询语句
DECLARE @strWhere NVARCHAR(MAX) ----用于存放查询条件
SELECT @timediff = getdate() ----设置开始时间
SET @startRow = (@pageIndex - 1) * @pageSize + 1 ----设置起始值
SET @endRow = @startRow + @pageSize - 1 ----设置结束值
----设置总记录数查询语句----
IF @strCondition is null or @strCondition='' ----没有设置查询条件
BEGIN
SET @SqlCounts = 'SELECT @RecordCount = COUNT(*) FROM ' + @tblName
END
ELSE ----有设置查询条件
BEGIN
SET @strWhere = ' WHERE ' + @strCondition
SET @SqlCounts = 'SELECT @RecordCount = COUNT(*) FROM ' + @tblName + @strWhere
END
----取得查询结果总数量-----
exec sp_executesql @SqlCounts,N'@RecordCount int out ',@RecordCount out
declare @tmpCounts int
if @RecordCount = 0
set @tmpCounts = 1
else
set @tmpCounts = @RecordCount
----取得分页总数 ----
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
----开始分页计算并取出相应数据----
----SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY contnet_keyword_addDate DESC) AS RowNumber FROM Public_content_keyword ) T WHERE T.RowNumber BETWEEN @startRow AND @endRow
IF @pageIndex = 1
BEGIN
IF @strCondition is null or @strCondition='' ----没有设置显示条件
BEGIN
SET @strTmp = 'SELECT TOP ' + CAST(@pageSize as VARCHAR(max)) + ' ' + @fldName + ' FROM ' + @tblName
END
ELSE ----有设置查询条件
BEGIN
SET @strTmp = 'SELECT TOP ' + CAST(@pageSize as VARCHAR(max)) + ' ' + @fldName + ' FROM ' + @tblName + @strWhere
END
END
ELSE
BEGIN
IF @strCondition is null or @strCondition='' ----没有设置显示条件
BEGIN
SET @strTmp = 'SELECT ' + @fldName + ' FROM (SELECT ' + @fldName + ',ROW_NUMBER() OVER (ORDER BY ' + @fldSort + ') AS RowNumber FROM '+ @tblName +') T WHERE T.RowNumber BETWEEN ' + CAST(@startRow as VARCHAR(max)) + ' AND ' + CAST(@endRow as VARCHAR(max))
END
ELSE ----有设置查询条件
BEGIN
SET @strTmp = 'SELECT ' + @fldName + ' FROM (SELECT ' + @fldName + ',ROW_NUMBER() OVER (ORDER BY ' + @fldSort + ') AS RowNumber FROM '+ @tblName + @strWhere +') T WHERE T.RowNumber BETWEEN ' + CAST(@startRow as VARCHAR(max)) + ' AND ' + CAST(@endRow as VARCHAR(max))
END
END
------返回查询结果-----
SET @strSql = @strTmp
EXEC(@strTmp)
SET @UsedTime = DATEDIFF(ms,@timediff,GETDATE())
SET NOCOUNT OFF
GO
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-616724/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16436858/viewspace-616724/