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
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: [EMAIL PROTECTED]
To: SQL
Sent: Tuesday, August 03, 2004 1:37 PM
Subject: Re: need help
Lori,
You can do it in a stored procedure. You will need to use a
cursor to execute your update once for each row in the tblPlan table,
unless someone else can come up with a way to do this in a single result
set. I once heard that most of the time, when you think you need a cursor
you really only need a better set query. However, right now I can't think
of how I would do that.
_____________________
Eric
_____________________
"Not only is the universe stranger than we imagine, it is stranger than we
can imagine." - Sir Arthur Eddington
Lori <[EMAIL PROTECTED]>
08/03/2004 01:03 PM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
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]
