In Wrox's Professional ASP 3.0, there where examples given for paging 
data.  One involved using a stored procedure to return only one page of 
data rather than the entire set.  What I would like to know is how you 
can use it in a tier'd approach... (see below)

Here is the example procedure:

create procedure MyProcedure
(
@Start int
@End int
)
as
declare @totalrecords int

create table #temp
(
record_id int identity(1,1),
customer_name varchar(20)
)

insert into #temp
select * from customer

select @totalrecords = count(*) from #temp
select * from #temp where record_id >= @Start and record_id <= @End

return @totalrecords

=======================
Here is the source within the component

public SqlDataReader MyFct(int iStart, int iEnd, out int iTotalRecords)
{
SqlConnection oConnection = new SqlConnection(sConnectionString);
SqlCommand oCommand = new SqlCommand("MyProcedure", oConnection);
oCommand.CommandType = CommandType.StoredProcedure;

SqlParameter prmReturnValue = oCommand.Parameters.Add("RETURN_VALUE", 
SqlDbType.Int);
prmReturnValue.Direction = ParameterDirection.ReturnValue;

SqlParameter prmStart = new SqlParameter("@Start", SqlDbType.Int);
prmStart.Value = iStart;
oCommand.Parameters.Add(prmStart);

SqlParameter prmEnd = new SqlParameter("@End", SqlDbType.Int);
prmEnd.Value = iEnd;
oCommand.Parameters.Add(prmEnd);

oConnection.Open();
SqlDataReader oSqlDataReader = oCommand.ExecuteReader
(CommandBehavior.CloseConnection);	

iTotalRecords = (int) oCommand.Parameters["RETURN_VALUE"].Value;

return oSqlDataReader;
}


How can I not only pass the SqlDataReader on to my ASP.NET page for use 
with the Repeater web server control but also retrieve the return value.  
I cannot retrieve the value because of the open connection and cannot 
close the connection because i need to pass the SqlDataReader on to the 
asp page. i suppose i could include the total number of records as a 
field but that is not very elegent

Also, I dont want to retrieve the entire dataset... only the portion that 
i need each time

Thanks in advance