Here is my first attempt:
alter proc usp_GetSubsetOfOrders @BatchSize int = 10, @BatchesToSkip as int = 0, @OrderBy as varchar(100)='orderdate' as
--9/8/01 Andy Warren
--Alternate method of returning a subset of records, typically used in 'paging' operations
--where you only show x records per page and do not want to return the entire recordset
--to the client.
declare @RowsToIgnore int
declare @Sql varchar(1000)
set @RowsToIgnore = @BatchesToSkip * @BatchSize
set @sql='select top ' + convert(varchar(10), @BatchSize) + ' OrderDate, ShipName, ShipCity from orders where orderid > (select max(orderid) from (select top ' + convert(varchar(10), @RowsToIgnore) + ' orderid from orders order by ' + @OrderBy + ') A) order by ' + @OrderBy
Just profiling using QA this appears to be a little faster than your solution.
AndySQLAndy - My Blog!Connect with me on LinkedInFollow me on Twitter