参照:http://www.cnblogs.com/panthervic/archive/2012/02/21/2361556.html
测试
declare @RecordCount int
declare @subject VARCHAR(200)
declare @sDate datetime
declare @eDate datetime
declare @sortName VARCHAR(20)
declare @sortOrder VARCHAR(10)
declare @PageIndex Int
declare @PageSize Int
--set @sortOrder='id'
--set @sortName='asc'
exec Get_ProClawerData_DataList_ByPage @subject,@sDate,@eDate,'id','asc',1,10,@RecordCount out
-- =============================================
-- Author: <Snow.Li>
-- Create date: <2012-11-27>
-- Description:
-- =============================================
alter PROCEDURE [dbo].[Get_ProClawerData_DataList_ByPage]
(
@subject VARCHAR(200) = NULL,
@sDate datetime = NULL,
@eDate datetime = NULL,
@sortName VARCHAR(20) = NULL,
@sortOrder VARCHAR(10) = NULL,
@PageIndex Int = NULL,
@PageSize Int = NULL,
@RecordCount Int = NULL OUTPUT
)
AS
BEGIN
DECLARE @Sql NVarChar(MAX)
declare @orderStr varchar(50)
DECLARE @Where VARCHAR(MAX)
SET @Where = ' A.[IsManaged]=0 '
IF @subject IS NOT NULL and @subject != ''
BEGIN
SET @Where = @Where + ' AND A.Subject like ''%'+@subject+'%'''
END
IF @sDate IS NOT NULL and @sDate != ''
BEGIN
SET @Where = @Where + ' AND A.CreateTime >= '+@sDate+''
END
IF @eDate IS NOT NULL and @eDate != ''
BEGIN
SET @Where = @Where + ' AND A.CreateTime <='+@eDate+''
END
set @Sql='
SELECT A.[Id]
, A.[KeyId]
, A.[ParentKeyId]
, A.[ReleaseTime]
, A.[Source]
, A.[Subject]
, A.[SubjectURL]
, A.[Brief]
, A.[SameNumber]
, A.[SameURL]
, A.[CreateTime]
, A.[CreateUserId]
, A.[UpdateTime]
, A.[UpdateUserId]
, A.[Version]
, A.[Type]
,A.[IsManaged]
FROM [ProClawerData] as A where'+@Where
set @orderStr = '['+@sortName+'] '+@sortOrder;
exec GetPage @sql,'ID',@PageIndex,@PageSize,@orderStr,@orderStr,@RecordCount output
END
GO
CREATE PROCEDURE [dbo].[GetPage]
@SQL [VARCHAR](8000), --SQL STATMENT
@PKEY [VARCHAR](100), --PRIMARY KEY NAME
@PAGENO [INT], --CURRENT PAGE NO
@PAGESIZE [INT], --PAGESIZE
@SORTBYALL [VARCHAR](1000), --SORT ALL DATA
@SORTBYPAGE [VARCHAR](1000), --SORT CURRENT PAGE DATA
@RECORDCOUNT [INT] OUTPUT --RECORD COUNT THIS PAGE RETURN
As
Begin
declare @inner_sql [NVARCHAR](4000)
if @PAGENO= 1 ---区分是否是第一页,如果页数大于1,则需要过滤掉每一页的第一条
begin
set @inner_sql = ' select * from ( '+ ' select row_number() over (order by '+ @SORTBYALL +') as row,* from (' + @SQL + ') t ) T_Table '+ ' where row between '+
convert(varchar,@PAGESIZE*(@PAGENO-1)) + ' and ' + convert(varchar,@PAGESIZE*@PAGENO) +' order by ' + @SORTBYPAGE
end
else
set @inner_sql = ' select * from ( '+ ' select row_number() over (order by '+ @SORTBYALL +') as row,* from (' + @SQL + ') t ) T_Table '+ ' where row between '+
convert(varchar,@PAGESIZE*(@PAGENO-1)) + ' and ' + convert(varchar,@PAGESIZE*@PAGENO) +' and row<> '+convert(varchar,@PAGESIZE*(@PAGENO-1)) +' order by ' + @SORTBYPAGE
exec(@inner_sql)
DECLARE @SQLSTR [NVARCHAR](4000);
SET @SQLSTR ='SELECT @COUNT = COUNT(1) FROM ('+@SQL+') AS AA';
EXECUTE SP_EXECUTESQL @SQLSTR,N'@COUNT [INT] OUT',@RECORDCOUNT OUT;
--select @RECORDCOUNT as @RECORDCOUNT
end
GO