自己用心写的 存储过程分页 给自己的平台用

 1 -- =============================================
 2 -- Author:  xf
 3 -- Create date: 2011-12-7
 4 -- Description: 1.0.0.0
 5 -- =============================================
 6 CREATE PROCEDURE [dbo].[Proc_Platform_Paging]
 7  -- Add the parameters for the stored procedure here 
 8  (
 9   @Table     varchar(100),    
10   @Fields    varchar(2000),  
11   @Where     nvarchar(2000), 
12   @PageSize  int,             
13    @PageIndex int,            
14   @Orders    varchar(500),   
15   @Count     int out         
16  )
17 AS
18 BEGIN
19  DECLARE @SQL NVARCHAR(4000)
20  declare @MaxID int
21     declare @MinID int
22     set @MaxID = @PageIndex*@PageSize;
23     set @MinID = (@PageIndex-1)*@PageSize;
24     set @Fields = 'ROW_NUMBER() OVER (ORDER BY '+@Orders+') AS RowNumber,'+@Fields;
25     set @Where = 'RowNumber>'+CAST(@MinID AS NVARCHAR)+' and RowNumber<='+CAST(@MaxID AS NVARCHAR)
26     if @PageIndex > 0 
27        begin
28        SET @SQL = 'SELECT * FROM (SELECT '+@Fields+' FROM '+@Table+') as T WHERE '+@Where+' order By '+@Orders
29        end
30     else 
31        begin
32        SET @SQL = 'SELECT * FROM (SELECT '+@Fields+' FROM '+@Table+') as T WHERE RowNumber>0 and RowNumber<='+CAST(@PageSize AS NVARCHAR)+' order By '+@Orders
33        end
34     print @sql
35     exec sp_executesql @SQL;
36 END

 

转载于:https://www.cnblogs.com/BinaryBoy/archive/2011/12/07/2279520.html