SQL分页存储过程——表名、返回的列、排序字段、排序类型、条件、页尺寸、页码 

 1 ALTER PROCEDURE [dbo].[SP_LGY_ICU_PAGECUT]
 2 @TableName varchar(255), -- 表名
 3 @DisplayFields varchar(1000) = '*', -- 需要返回的列 
 4 @OrderFields varchar(255)='''', -- 排序的欄位名
 5 @OrderType bit = 0, -- 設置排序類型, 非 0 值則降冪
 6 @StrWhere varchar(1500) = '''', -- 查詢條件 (注意: 不要加 where)
 7 @PageSize int = 10, -- 頁尺寸
 8 @PageIndex int OUTPUT, -- 頁碼
 9 @AllRowCount int OUTPUT,
10 @AllPageCount int OUTPUT
11 
12 AS
13 
14 declare @strSQL nvarchar(4000) -- 主語句
15 declare @strTmp nvarchar(110) -- 臨時變數
16 declare @strOrder nvarchar(400) -- 排序類型
17 
18 
19 begin
20     declare   @d   datetime   
21     set   @d=getdate() 
22     if @StrWhere !=''''
23         set @strSQL = N'select @AllRowCount=count(*)  from [' + @TableName + '] with(nolock) where '+@StrWhere 
24     else
25         set @strSQL =N'select @AllRowCount=count(*)  from [' + @TableName + ']'+' with(nolock) '
26     exec sp_executesql @strSQL,N'@AllRowCount int out',@AllRowCount out
27 
28     IF ISNULL(@PageSize,0)<1
29        SET @PageSize=10
30     IF @AllPageCount % @PageSize=0
31        SET @AllPageCount=@AllRowCount/@PageSize
32     ELSE IF @PageSize=1
33        SET @AllPageCount=@AllRowCount/@PageSize
34     ELSE
35        SET @AllPageCount=@AllRowCount/@PageSize+1
36     IF ISNULL(@PageIndex,0)<1
37        SET @PageIndex=1
38     ELSE IF ISNULL(@PageIndex,0)>@AllPageCount
39        SET @PageIndex=@AllPageCount
40 
41     if @OrderType != 0--采用升降序的何種方式排序，不是0則降序
42     begin
43         set @strTmp = '<(select min'
44         set @strOrder = ' order by ' + @OrderFields +' desc'
45     end
46     else--是0則升序,默認為升序
47     begin
48         set @strTmp = '>(select max'
49         set @strOrder = ' order by ' + @OrderFields +' asc'
50     end
51     
52     if @PageIndex = 1
53     begin
54         if @StrWhere != ''''
55         set @strSQL = 'select top ' + str(@PageSize) +' '+@DisplayFields+ 'from [' + @TableName + '] with(nolock) where ' + @StrWhere + ' ' + @strOrder
56         else
57         set @strSQL ='select top '+ str(@PageSize) +' '+@DisplayFields+ 'from ['+ @TableName + '] with(nolock) '+ @strOrder
58         --如果是第一頁就執行以上代碼，這樣會加快執行速度
59     end
60     else
61     begin
62         --以下代碼賦予了@strSQL以真正執行的SQL代碼
63         set @strSQL = 'select top ' + str(@PageSize) +' '+@DisplayFields+ ' from ['+ @TableName + '] with(nolock) where ' + @OrderFields + '' + @strTmp + '(tabKey) from (select top '+ str((@PageIndex-1)*@PageSize) + ' '+ @OrderFields + ' as tabKey from [' + @TableName + '] with(nolock) ' + @strOrder + ') as tblTmp)'+ @strOrder
64         if @StrWhere != ''''
65         set @strSQL = 'select top ' + str(@PageSize) +' '+@DisplayFields+ ' from ['+ @TableName + '] with(nolock) where ' + @OrderFields + '' + @strTmp + '(tabKey) from (select top '+ str((@PageIndex-1)*@PageSize) + ' '+ @OrderFields + ' as tabKey from [' + @TableName + '] with(nolock) where ' + @StrWhere + ' '+ @strOrder + ') as tblTmp) and ' + @StrWhere + ' ' + @strOrder
66     end
67 end
68 Print @StrSql
69 exec (@strSQL)

 

posted @ 2016-03-23 10:32  淘小人  阅读(455)  评论(0)    收藏  举报