sp_GetOrders(@subset int = 0, @rowcount int = 10, @orderby varchar(100) = 'OrderDate')

AS

--Use the Northwind Database for this example

BEGIN

SET NOCOUNT ON

DECLARE @ABSPOS INT

SET @ABSPOS = @SUBSET * @ROWCOUNT + 1

CREATE TABLE

#ORDERS

(OrderDate DATETIME, ShipName VARCHAR(50), ShipCity VARCHAR(50))

DECLARE

@OrderDate DATETIME,

@ShipName VARCHAR(50),

@ShipCity VARCHAR(50)

EXEC('DECLARE cOrders SCROLL CURSOR FOR

SELECT OrderDate, ShipName, ShipCity FROM Orders ORDER BY ' + @orderby)

OPEN cOrders

DECLARE @COUNTER INT

SET @COUNTER = 0

FETCH ABSOLUTE @ABSPOS FROM cOrders INTO @OrderDate, @ShipName, @ShipCity

WHILE( @@FETCH_STATUS = 0 AND @COUNTER < @ROWCOUNT)

BEGIN

INSERT #ORDERS VALUES(@OrderDate, @ShipName, @ShipCity)

SET @COUNTER = @COUNTER + 1

FETCH FROM cOrders INTO @OrderDate, @ShipName, @ShipCity

END

CLOSE cOrders

DEALLOCATE cOrders

SELECT * FROM #ORDERS

SET NOCOUNT OFF

END

GO