1 CREATE Procedure dbo.Example
  2 @sortExpr	varchar(20),
  3 @sortType         varchar(4),  
  4 @pageNum	int = 1,
  5 @pageSize	int = 9999
  6 AS
  7 /******************************************************************************
  8 **		Parameters:
  9 
 10 		@sortExpr	varchar(20), 	 - поле сортировки
 11 		@sortType   varchar(4),  	 - направление сортировки
 12 		@pageNum	int = 1,	 - № страницы
 13 		@pageSize	int = 9999	 - размер страницы
 14 **
 15 *******************************************************************************/
 16 declare @spageNum varchar(30)
 17 declare @spageSize varchar(30)
 18 set @spageNum = cast(@pageNum as varchar(30))
 19 set @spageSize = cast(@pageSize as varchar(30))
 20 
 21 declare @sql varchar(8000)
 22 set @sql = '
 23 DECLARE @firstRecord int, @lastRecord int
 24 SET @firstRecord = ('+ @spageNum + ' - 1) * ' +@spageSize+ ' + 1
 25 SET @lastRecord = ('+ @spageNum +' *'+ @spageSize+')
 26 
 27 select
 28  -- всякие поля ,
 29 identity(int, 1, 1) as paging_id
 30 into #paging
 31 from
 32 всякие таблицы order by '
 33 
 34 set @sql = @sql + @sortExpr + @sortType + '
 35 
 36 select @@RowCount as RowsCount
 37 
 38 select * from #paging WHERE #paging.paging_id BETWEEN @firstRecord and @lastRecord'
 39 exec (@sql)
 40