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)
