Returning a Subset of a Recordset

,

Returning a Subset of a Recordset

Stateless programming can be tricky... In case you ever need to

return a specified subset of a query without first returning the

entire recordset, here's a method.  (This tip was given to me by a co-worker, Jason Rice).

CREATE PROCEDURE

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

Rate

Share

Share

Rate