通用分页存储过程(多表,多排序字段)

GO /****** 对象: StoredProcedure [dbo].[SMSPagination] 脚本日期: 12/22/2009 16:07:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[SMSPagination] ( @Table_Name varchar(5000), --表名 @Primary_Key varchar(50), --主键Primary_Key @Show_Fields varchar(1000), --查找的字段 Show_Fields @Order_Field varchar(1000), --排序字段 Order_Field @OrderType bit = 0, --排序方式 1为 DESC, 0为 ASC 默认值是升序 OrderType @where_Condition varchar(1000), --筛选条件,不带where_Condition (不加Where) @Page_Size int = 10, --页大小 @Page_Index int = 1, --页码 @TotalCount int=0, -- 记录总数 初始值为0,当访问PageIndex==1,返回总数 @ReTotalCount int output -- 记录总数 初始值为0,当访问PageIndex==1,返回总数 -- @strSql varchar(5000) = '' OUTPUT --返回执行的SQL语句 ) AS BEGIN DECLARE @Start_Number int DECLARE @End_Number int DECLARE @TopN_Number int DECLARE @Total_Number int DECLARE @sSQL varchar(8000) DECLARE @SqlCounts NVARCHAR(4000) --用于存放总记录数查询语句 --设置显示字段 如果传入为空值采用全部显示 IF(@Show_Fields is NULL OR Len(@Show_Fields)=0) BEGIN SET @Show_Fields='*' END --设置总记录数查询语句 IF @where_Condition is null or len(@where_Condition)=0 --没有设置查询条件 BEGIN SET @SqlCounts ='SELECT @Total_Number=COUNT(1) FROM ' + @Table_Name END ELSE--有设置查询条件 BEGIN SET @where_Condition = @where_Condition SET @SqlCounts = 'SELECT @Total_Number=COUNT(1) FROM ' + @Table_Name +' WHERE '+ @where_Condition END print @SqlCounts print 'out符合数据条数:'+convert( CHAR( 20 ), @TotalCount, 104 ) ----取得查询结果总数量----- --IF (@Order_Field is NULL OR Len(@Order_Field)=0) if(@Page_Index = 1) Begin exec sp_executesql @SqlCounts,N'@Total_Number int out ',@Total_Number out End else begin SET @Total_Number=@TotalCount end --设置返回参数并验证是否有效 SET @ReTotalCount=@Total_Number -------------------------------------------------------- IF (@ReTotalCount IS NULL) BEGIN RAISERROR ('Parameter ''@Total_Number'' can not be null.' , 16, 1) WITH NOWAIT RETURN 1 END -------------------------------------------------------- --设置开始位置 SET @Start_Number =(@Page_Index-1) * @Page_Size IF @Start_Number<=0 SET @Start_Number=0 --设置结束位置 SET @End_Number=@Start_Number+@Page_Size IF @End_Number>@Total_Number SET @End_Number=@Total_Number --设置返回数目 SET @TopN_Number=@End_Number-@Start_Number IF @TopN_Number<=0 SET @TopN_Number=0 print 'out符合数据条数:'+convert( CHAR( 20 ), @Total_Number, 104 ) print '返回符合数据条数:'+convert( CHAR( 20 ), @TopN_Number, 104 ) print '返回数据开始位置:'+convert( CHAR( 20 ), @Start_Number, 104 ) print '返回数据结束位置:'+convert( CHAR( 20 ), @End_Number, 104 ) print '符合数据的总条数:'+convert( CHAR( 20 ), @Total_Number, 104 ) --设置排序字段 如果传入为空值则采用主键作为排序字段 IF (@Order_Field is NULL OR Len(@Order_Field)=0) begin SET @Order_Field=@Primary_Key end IF @OrderType=0 --按升序排序 BEGIN IF (@where_Condition IS NULL or LEN(@where_Condition)=0) BEGIN SET @sSQL='SELECT '+@Show_Fields+' FROM '+@Table_Name+' WHERE '+@Primary_Key+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Primary_Key+' FROM '+@Table_Name+' WHERE '+@Primary_Key+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Primary_Key+' FROM '+@Table_Name+ ' ORDER BY '+@Order_Field+' ASC) order by '+@Order_Field+' DESC) order by '+@Order_Field+' ASC' END ELSE BEGIN SET @sSQL='SELECT '+@Show_Fields+' FROM '+@Table_Name+' WHERE '+@Primary_Key+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Primary_Key+' FROM '+@Table_Name+' �