WEB LISTING 1: Code That Retrieves Specified Rows 
Within a Resultset Based on Parameter Values

/*
-------------------------- 
================================== 
--RECORDS x->y from customers.
--Useful for returning subset of records for HTML tables. 
==================================== 
-------------------------- 
Sourcesmith Industries Inc. 
Bill Stockton 604/988-6370 ext 104 
bills@sourcesmith.com 
billstockton@hotmail.com 
-------------------------- 
*/ 
USE northwind 
GO

CREATE PROCEDURE spr_customers_by_CompanyName_find(@first_record int, 
@return_records int) 
AS

DECLARE @sql nvarchar(512)

CREATE TABLE #customers (customerID nchar(5))

--Insert into this table all the records up to the last record. 
SELECT @sql =   'INSERT INTO #customers ' + 
                'SELECT TOP ' + CAST((@first_record + @return_records 
-1) AS varchar(16)) + ' customerID ' + 
              '  FROM Customers ORDER BY CompanyName ' 
EXEC(@sql)

--Delete from table all records previous to first record.
SELECT @sql = 'DELETE FROM #customers WHERE customerID in (' + 
              ' SELECT TOP ' + CAST((@first_record-1) AS varchar(16)) + 
' customerID ' + 
              ' FROM Customers ORDER BY CompanyName) ' 
EXEC(@sql)

SELECT * FROM Customers c 
INNER JOIN #customers t ON t.customerID = c.customerID

GO 
/*----------------------------------------------- 
e.g., 
*/ 
EXEC dbo.spr_customers_by_CompanyName_find 20,20 
GO 
 <<retrieving_specified_rows.sql>> <<retrieving_specified_rows.sql>>