How to break a result of a query in SP into batches ?
From: Paul fpvt2 (Paulfpvt2_at_discussions.microsoft.com)
Date: 12/09/04
- Next message: Zeeshan: "Re: Brackets and special characters -- Brain Teaser"
- Previous message: --CELKO--: "RE: Which query is more optimized......."
- Next in thread: Anith Sen: "Re: How to break a result of a query in SP into batches ?"
- Reply: Anith Sen: "Re: How to break a result of a query in SP into batches ?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 9 Dec 2004 13:33:07 -0800
I am using VB6 with ADO accessing a SQL Server 2000 database that has about
8.5 million records. The database has 1 table, and the table has 4 columns.
CREATE TABLE [dbo].[Packet] (
[PACKET_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PACKET_TIME] [datetime] NOT NULL ,
[PACKET_CONTRACT] [varchar] (8) NOT NULL ,
[PACKET_DATA] [text] )
[PACKET_ID] is the primary key (non clustered), and I have a clustered
indexed for column [PACKET_TIME].
I have a stored procedure to get data for the last x seconds, minutes, hours
or days. I would like to break the result of the query into batches.
I followed the example at
http://www.4guysfromrolla.com/webtech/062899-1.shtml (sp_PagedItems), but I
am confused.
In sp_PagedItems, I copied the result of a query from table Packet into the
temp table.
Let's say I run sp_PagedItems to return records from the last 1 hour, and
let's say it returns 100 records. How can I call sp_PagedItems so that I can
get the result value of the query in 10 separate times ?
If I call sp_PagedItems with parameter @Page = 1, it only returns the first
10 records, but if I call sp_PagedItems again, the query runs again, which I
do not want, because the result will be different every time I run the query
to get records for the last 1 hour. I want the result of the same query from
the first time to be returned 10 times. How can I do this ?
Thank you very much.
This is from sp_PagedItems :
CREATE PROCEDURE sp_PagedItems
(
@Page int,
@RecsPerPage int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
ptime datetime,
pdata text
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (ptime,pdata)
select packet_time,packet_data from packet where packet_time >=
dateadd(hour, -1, getdate()) and packet_contract = 'ABCD'
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
set @Page = 2
set @RecsPerPage = 100
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
SET NOCOUNT OFF
This is my VB code:
sSql = "exec sp_PagedItems @Page=1,@RecsPerPage=10"
Set m_rs = m_adoCon.Execute(sSql)
x = 0
If Not m_rs.EOF Then
If CInt(m_rs("MoreRecords")) > 0 Then
bolLastPage = False
Else
bolLastPage = True
End If
End If
'the following only returns the first 10 records of query select
packet_time,packet_data from packet where packet_time >= .., if I run
sp_PagedItems again, the query select packet_time,packet_data from packet
where packet_time >= ..will run again, which I do not want.
Do While Not m_rs.EOF
msgbox m_rs("ptime") & & "," & m_rs("pdata")
m_rs.MoveNext
Loop
Thanks a lot.
- Next message: Zeeshan: "Re: Brackets and special characters -- Brain Teaser"
- Previous message: --CELKO--: "RE: Which query is more optimized......."
- Next in thread: Anith Sen: "Re: How to break a result of a query in SP into batches ?"
- Reply: Anith Sen: "Re: How to break a result of a query in SP into batches ?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
