Here is the code:
CREATE PROCEDURE getDpCount AS
/* declare local variables used for fetch */
declare @strPlanTestNo varchar(50)
declare @dpCount int
/* declare the cursor to get each book written
by given author */
declare testno cursor for
select strPlanTestNo
from tblPlan
open testno
FETCH NEXT FROM testno
into @strPlanTestNo
/* if cursor result set is not empty, then process
each row of information */
while (@@FETCH_STATUS = 0)
begin
declare dpcount cursor for
Select count(intDatapointID) as dpcount from tblDatapoints where strTestNo = @strPlanTestNo
open dpcount
FETCH NEXT FROM dpcount
into @dpCount
while (@@FETCH_STATUS = 0)
begin
Update tblPlan set intPlanDpCount= @dpCount
WHERE strPlanTestNo = @strPlanTestNo
FETCH NEXT FROM dpcount
into @dpCount
end
CLOSE dpcount
DEALLOCATE dpcount
FETCH NEXT FROM testno
into @strPlanTestNo
end
CLOSE testno
DEALLOCATE testno
Select intPlanDpCount from tblPlan
GO
----- Original Message -----
From: Lori
To: CF-Community
Sent: Tuesday, August 03, 2004 10:36 AM
Subject: need help
I am reading an excel file into a table. There is a field in the table for # of datapoints that I need to get from counting the records in another table that match the test number. Tables are
tblPlan
strTestNo
intDpCount
tblDatapoints
intDatapointID
strTestNo
so I need to read through tblDatapoints and get the count of the datapoints that match that test number and update tblPlan with that number. I could easily write a CF page but I need this done daily and our hoster hasn't gotten the scheduled task working. Is there any way I can do this with a stored procedure?
Lori
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
