SQL 简单分页查询

0001 use tzyj_motortest_data
0002 go
0003 if exists(select * from sysobjects where name = N'pGetPageDataFromTable')
0004     drop procedure pGetPageDataFromTable
0005 GO
0006 
0007 create procedure pGetPageDataFromTable
0008 (
0009     @tableName varchar(1000),
0010     @pageSize int,
0011     @currentPageIndex int,
0012     @indexFieldName nvarchar(100)
0013 )
0014 AS
0015     declare @sqlText nvarchar(1000)
0016 
0017 begin
0018     set nocount on
0019     if (@tableName is null) 
0020         begin
0021             RaisError('未提供分页的源表表名!!', 16, 1)
0022             return -100
0023         end
0024 
0025     set @sqlText = 'select top ' + cast(@pageSize as nvarchar(10)) + ' * from ' + @tableName
0026                 + ' where ' + @indexFieldName + ' not in (select top ' 
0027                 + cast(@pageSize * (@currentPageIndex - 1) as nvarchar(10)) + ' ' + @indexFieldName
0028                 + ' from ' + @tableName + ' order by ' + @indexFieldName + ') '
0029                 + ' order by ' + @indexFieldName
0030     print @sqlText
0031     exec sp_executeSQL @sqlText
0032 
0033 end
0034 GO
0035 
0036 -- select count(*) from testCrvData
0037 -- select top 10 * from testCrvDate
0038 
0039 exec pGetPageDataFromTable 'testCrvData', 30, 100000, N'crvID'