话不多说,请看代码:

   Procedure Pagination_basic:  ALTER PROCEDURE [qiancheng].[Pagination_basic] (  @Table_name VARCHAR (255),  --name of table  @Rows_target VARCHAR (1000) = '*',  --search rows   @Rows_condition VARCHAR (1000) = '',  --the condition to find target (no where)  @Rows_order VARCHAR (255) = '',  --the rows to rank  @Order_type INT = 0,  -- *Q*C* 0 normal 1 down  @PageSizes INT = 10,  --the size of each page  @PageIndex INT = 1,  --current page  @ShowPages INT,  --whether show the pages *Q*C* 1-yes 0-no  @ShowRecords INT,  --whether show the record *Q*C* 1-yes 0-no  @Records_total INT OUTPUT,  --returned total records  @Pages_total INT OUTPUT --returned total pages  ) AS  DECLARE @MainSQL_QC nvarchar (2000) --Main  sentence  DECLARE @Var_QC VARCHAR (100) --Temporary variate  DECLARE @Order_QC VARCHAR (400) --the sort to rank  SET @Records_total = 0  SET @Pages_total = 0  IF @ShowRecords = 1  OR @ShowPages = 1  BEGIN  IF @Rows_condition != ''  SET @MainSQL_QC = 'select @Records_total = count(1) from [' + @Table_name + '] where ' +@Rows_condition  ELSE  SET @MainSQL_QC = 'select @Records_total = count(1) from [' + @Table_name + ']' EXEC sp_executesql @MainSQL_QC,   N'@Records_total int out' ,@Records_total OUTPUT  END  IF @ShowPages = 1  BEGIN  IF @Records_total <= @PageSizes  SET @Pages_total = 1  ELSE  BEGIN  SET @Pages_total = @Records_total /@PageSizes  IF (@Records_total %@PageSizes) > 0  SET @Pages_total = @Pages_total + 1  END  END  IF @Order_type = 1  BEGIN  SET @Var_QC = '<(select min'  SET @Order_QC = ' order by [' + @Rows_order + '] desc'  END  ELSE  BEGIN  SET @Var_QC = '>(select max'  SET @Order_QC = ' order by [' + @Rows_order + '] asc'  END  IF @PageIndex = 1  BEGIN  IF @Rows_condition != ''  SET @MainSQL_QC = 'select top ' + str(@PageSizes) + ' ' +@Rows_target + ' from [' + @Table_name + '] where ' + @Rows_condition + ' ' + @Order_QC  ELSE  SET @MainSQL_QC = 'select top ' + str(@PageSizes) + ' ' +@Rows_target + ' from [' + @Table_name + '] ' + @Order_QC  END  ELSE  BEGIN  IF @Rows_condition != ''  SET @MainSQL_QC = 'select top ' + str(@PageSizes) + ' ' +@Rows_target + ' from [' + @Table_name + '] where [' + @Rows_order + ']' + @Var_QC + '([' + @Rows_order + ']) from (select top ' + str((@PageIndex - 1) *@PageSizes) + ' [' + @Rows_order + '] from [' + @Table_name + '] where ' + @Rows_condition + ' ' + @Order_QC + ') as Tmep_QC) and ' + @Rows_condition + ' ' + @Order_QC  ELSE  SET @MainSQL_QC = 'select top ' + str(@PageSizes) + ' ' +@Rows_target + ' from [' + @Table_name + '] where [' + @Rows_order + ']' + @Var_QC + '([' + @Rows_order + ']) from (select top ' + str((@PageIndex - 1) *@PageSizes) + ' [' + @Rows_order + '] from [' + @Table_name + ']' + @Order_QC + ') as Tmep_QC)' + @Order_QC  END EXEC (@MainSQL_QC)  

调用:execute pagination_basic ‘UserDetail’,’*’,”,’id’,’1′,’5′,’1′,’1′,’1′,”,”

主要是末尾的语句,拆分下来便是这样:

select top 每页数 列名 from [表名] where [排序字段名] <    –1 倒序输出若列 小于之前页数的最小值

(select min ( [排序字段名] )from –2 获得一个指定列名中的最小值并输出

(select top (当前页-1)*每页数 [排序字段名] from [表名] where [条件] [排序类型]) –3 选择之前页数总数据倒序输出

as Tmep_QC)–4 建立一个名为Tmep_QC的临时表–2 获得一个指定列名中的最小值并输出

and [条件] [排序类型]–1 倒序输出若列 小于之前页数的最小值

以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持沃谷博客!

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注