高效通用分页存储过程 多表查询

DECLARE	@return_value int,
		@TotalCount int,
		@TotalPageCount int

EXEC	@return_value = [dbo].[UP_GetRecordByPageOrder]
		@tblName = N'dbo.task AS ts  LEFT JOIN dbo.task_scinfo AS sc ON ts.id = sc.sc_rwid',
		@fldName = N'ts.id,ts.class_name,ts.task_name,ts.file_url,sc.id AS scid',
		@strWhere = N'ts.islock = 0',
		@OrderfldName = N'ts.id asc',
		@PageSize = 10,
		@PageIndex = 0,
		@TotalCount = @TotalCount OUTPUT,
		@TotalPageCount = @TotalPageCount OUTPUT

SELECT	@TotalCount as N'@TotalCount',
		@TotalPageCount as N'@TotalPageCount'

SELECT	'Return Value' = @return_value

GO

UP_GetRecordByPageOrder

CREATE PROC [dbo].[UP_GetRecordByPageOrder]
/*
nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2009.4.22 
敬告：适用于单一主键或存在唯一值列的表或视图
ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
*/
@tblName VARCHAR(255),    --表名
@fldName VARCHAR(255),    --显示列名，如果是全部字段则为*
@strWhere VARCHAR(1000),        --查询条件 不含'where'字符，如id>10 and len(userid)>9
@OrderfldName VARCHAR(255),        --排序(多列排序情况) 不含'order by'字符，如id asc,userid desc，必须指定asc或desc
--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@PageSize INT =10,              --每页输出的记录数
@PageIndex INT =1,              --当前页数
@IsRecount bit =0,
@RecorderCount INT =0,          --记录总数 1:会返回总记录
@TotalCount INT =0 OUTPUT,      --记返回总记录
@TotalPageCount INT =0 OUTPUT  --返回总页数

AS
----------------参数验证-----------------
SET NOCOUNT ON
--总页数
IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0
--排序方式
SET @OrderfldName = RTRIM(LTRIM(@OrderfldName))
--字段列表
--SET @fldName = REPLACE(RTRIM(LTRIM(@fldName)),' ','')
--排序表达式整理
WHILE CHARINDEX(', ',@OrderfldName) > 0 OR CHARINDEX(' ,',@OrderfldName) > 0
  BEGIN
    SET @OrderfldName = REPLACE(@OrderfldName,', ',',')
    SET @OrderfldName = REPLACE(@OrderfldName,' ,',',')
  END
IF ISNULL(@tblName,'') = '' OR ISNULL(@fldName,'') = ''
OR @RecorderCount  < 0 OR @PageSize < 0 OR @PageIndex < 0
BEGIN
PRINT('ERR_00')
RETURN
END

-------------------处理排序表达式----------------
BEGIN
IF (UPPER(RIGHT(@OrderfldName,4))!=' ASC' AND UPPER(RIGHT(@OrderfldName,5))!=' DESC')
BEGIN PRINT('ERR_02') RETURN END
END
DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000)
DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 VARCHAR(1000)
DECLARE @temptable VARCHAR(1000)
DECLARE @Sql VARCHAR(8000)
DECLARE @SqlCount NVARCHAR(4000)

SET @temptable=@tblName

IF ISNULL(@strWhere,'') = ''
BEGIN
SET @new_where1 = ' '
SET @new_where2 = ' WHERE  '
END
ELSE
BEGIN
SET @new_where1 = ' WHERE ' + @strWhere
SET @new_where2 = ' WHERE ' + @strWhere + ' AND '
END

BEGIN
SET @new_order1 = ' ORDER BY ' + @OrderfldName
END

BEGIN
SET @new_order1 = ' ORDER BY ' + @OrderfldName
SET @new_order2 = @OrderfldName + ','
SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)

IF @fldName <> '*'
BEGIN
SET @new_order3 = REPLACE(REPLACE(@OrderfldName + ',','ASC,',','),'DESC,',',')
SET @fldName = ',' + @fldName
WHILE CHARINDEX(',',@new_order3)>0
  BEGIN
  IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@fldName+',')>0
  BEGIN
  SET @fldName =
  @fldName + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))
  END
  SET @new_order3 =
  SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
  END
  SET @fldName = SUBSTRING(@fldName,2,LEN(@fldName))
END
END

------根据所要取的记录数@RecorderCount取出总记录数及总页数
SET @SqlCount = 'SELECT @TotalCount=COUNT(1),@TotalPageCount=CEILING((COUNT(1)+0.0)/'
+ CAST(@PageSize AS VARCHAR)+') FROM ' + @temptable + @new_where1

IF @RecorderCount  = 0
 BEGIN
  EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
  @TotalCount OUTPUT,@TotalPageCount OUTPUT
 END
ELSE
 BEGIN
  SELECT @TotalCount = @RecorderCount,@TotalPageCount=CEILING((@TotalCount+0.0)/@PageSize)
  set @SqlCount='drop table temp select TOP('+CAST(@TotalCount As varchar)+') * into temp from '+@temptable+ @new_where1 + @new_order1
  exec sp_executesql @SqlCount
  set @temptable='temp'
 END
--------------------------取数据------------------------
IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) OR @PageIndex =0
 BEGIN
  IF @PageIndex = 1 OR @PageIndex =0 --返回第一页数据
   BEGIN
    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @fldName + ' FROM '
    + @temptable + @new_where1 + @new_order1
   END
  IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)  --返回最后一页数据
   BEGIN
    SET @PageIndex =  CEILING((@TotalCount+0.0)/@PageSize)
    SET @Sql = 'SELECT TOP ' +STR(@TotalCount-@PageSize*(@TotalPageCount-1)) + ' * FROM ('
     + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
     + ' ' + @fldName + ' FROM '
     + @temptable + @new_where1 + @new_order2 + ' ) AS TMP '
     + @new_order1
   END
 END
ELSE
 BEGIN
---------------------------输出数据-------------------------------
  IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向检索
   BEGIN
    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' * FROM ( '
    + 'SELECT TOP ' + STR(@PageSize) + ' * FROM ( '
    + ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @fldName
    + ' FROM ' + @temptable + @new_where1 + @new_order1 + ' ) AS TMP '
    + @new_order2 + ' ) AS TMP ' + @new_order1
   END
  ELSE  --反向检索
   BEGIN
    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' * FROM ( '
    + 'SELECT TOP ' + STR(@PageSize) + ' * FROM ( '
    + ' SELECT TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @fldName
    + ' FROM ' + @temptable + @new_where1 + @new_order2 + ' ) AS TMP '
    + @new_order1 + ' ) AS TMP ' + @new_order1
   END
 END

EXEC(@Sql)