T-SQL分页存储过程(2)

来源：未知责任编辑：责任编辑发表时间:2015-10-08 14:16　点击:次

35 + @fieldName +'] from ['+ @tableName+'] where '

36 + @strCondition + '' + @strOrder + ') as tableTemp) and'

37 + @strCondition + '' + @strOrder

38 if @pageIndex = 1

39 begin

40 set @strTemp =''

41 if @strCondition !=''

42 set @strTemp =' where ('+ @strCondition + ')'

44 set @strSQL ='select top'+ str(@pageSize)+ ' * from ['

45 + @tableName+ ']'+ @strTemp+ '' + @strOrder

46 end

47 Execute(@strSQL)

48 Go

分页存储二：

1 create procedure pro_GetRecordForPaging

2 @tableName varchar(200),--表名

3 @pageSize int, --每页显示记录数

4 @pageIndex int, --当前页码

5 @colName varchar(30) --排序的字段名

6 as www.2cto.com

7 begin

8 declare @strSQL varchar(300)

9 if @pageIndex = 1

10 set @strSQL ='select top ['+cast(@pageSize as varchar(20)) + ']'

11 + ' * from ['+ @tableName + ']'

12 + ' order by ['+ @colName + ']'

13 else

14 set @strSQL='select top ['+cast(@pageSize as varchar(20)) + ']'

15 + ' * from ['+ @tableName + ']'

16 + ' where ['+ @colName + ']'

17 + ' not in ( select top ['

18 + cast((@pageIndex-1)*pageSize as varchar(20)) + ']'

19 + @colName + ' from ['+ @tableName + ']'

20 + 'order by ['+ @colName +'])'

21 + 'order by ['+ @colName +']'