Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 10144 | 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

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones