Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Returning a Subset of a Recordset

By Jon Winer,

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
 
Total article views: 10151 | Views in the last 30 days: 1
 
Related Articles
FORUM

counters

counters help

FORUM

Performanc Counters

Performanc Counters

FORUM

check counters

check counters

FORUM

SQL 2005 Perfmon Objects/Counters

Perfmon Counters

FORUM

UDF to create a counter

row counter

Tags
advanced querying    
t-sql    
 
Contribute