SQL Server 2008 分頁預存程序

標籤：style   blog   color   ar   2014   art   cti   div   

在用的，已經不知道出處了，如果有侵權請告訴我，我立刻刪掉。

USE [ActivaOnlineSupport]GO/****** Object:  StoredProcedure [dbo].[PROCE_PAGECHANGE]    Script Date: 08/13/2014 13:45:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[PROCE_PAGECHANGE]( @TableName varchar(50),            --表名 @ReFieldsStr varchar(200) = ‘*‘,   --欄位名(全部欄位為*) @OrderString varchar(200),         --排序欄位(必須!支援多欄位不用加order by) @WhereString varchar(500) =N‘‘,  --條件陳述式(不用加where) @PageSize int,                     --每頁多少條記錄 @PageIndex int = 1 ,               --指定當前為第幾頁 @TotalRecord int output            --返回總記錄數)AS  BEGIN         --處理開始點和結束點    Declare @StartRecord int;    Declare @EndRecord int;     Declare @TotalCountSql nvarchar(500);     Declare @SqlString nvarchar(2000);        set @StartRecord = (@PageIndex-1)*@PageSize + 1    set @EndRecord = @StartRecord + @PageSize - 1     SET @TotalCountSql= N‘select @TotalRecord = count(*) from ‘ + @TableName;--總記錄數語句    SET @SqlString = N‘(select row_number() over (order by ‘+ @OrderString +‘) as rowId,‘+@ReFieldsStr+‘ from ‘+ @TableName;--查詢語句    --    IF (@WhereString! = ‘‘ or @WhereString!=null)        BEGIN            SET @TotalCountSql=@TotalCountSql + ‘  where ‘+ @WhereString;            SET @SqlString =@SqlString+ ‘  where ‘+ @WhereString;                    END    --第一次執行得到    --IF(@TotalRecord is null)    --   BEGIN           EXEC sp_executesql @totalCountSql,N‘@TotalRecord int out‘,@TotalRecord output;--返回總記錄數    --  END    ----執行主語句    set @SqlString =‘select * from ‘ + @SqlString + ‘) as t where rowId between ‘ + ltrim(str(@StartRecord)) + ‘ and ‘ +  ltrim(str(@EndRecord));    Exec(@SqlString)    END