@@FETCH_STATUS Problem

[Mike]

I have a table with 15000 records in it where I need to update a
couple of columns from a calculation. I would normally do this through
ADO and a bit of VB but I thought I would have a go at doing this
using a cursor in a stored procedure having never done it before. I
have followed the advice on the web but whenever I run the procedure
it gets between 100 and 1500 records into the table and then just
stops. The records have been imported from an Excel spread sheet.
Below is the SP:

ALTER PROCEDURE BriminghamSP
AS
declare @ID int
declare @postcode char(8)
declare @RecordCount int
declare @RiskFactor decimal(18,4)
declare @Risk varchar(10)
declare @Homes int
declare @Leads int
declare @Easting float
declare @Northing float
declare myCursor CURSOR FOR select id, postcode1 from SPBirmingham
open myCursor
FETCH NEXT FROM myCursor into @id, @postcode
while (@@FETCH_STATUS <> -1)
begin
if (@@FETCH_STATUS <> -2)
begin
set @Risk = ''
set @RiskFactor = 0
set @Homes = 0
set @Leads = 0
SELECT @RecordCount = count(postCode) FROM
dbo.EnglandMappointLeads2001to2005 WHERE postCode = @postCode
if @RecordCount > 0
begin
SELECT @Easting = E, @Northing = N FROM
dbo.EnglandMappointLeads2001to2005 WHERE postCode = @postCode
SELECT @Leads = sum(Leads), @Homes = sum(Homes) FROM
dbo.EnglandMappointLeads2001to2005 WHERE sqrt(square(@Easting-E)
+square(@Northing-N))<=500
if (@Leads > 0 and @Homes > 0)
begin
set @RiskFactor = convert(decimal(18,4),@Leads)/
convert(decimal(18,4),@Homes)
end
if @RiskFactor < 0.0022
begin
set @Risk = 'Low'
end
if (@RiskFactor > 0.0022 and @RiskFactor <= 0.0044)
begin
set @Risk = 'Medium'
end
if (@RiskFactor > 0.0044 and @RiskFactor <= 0.0088)
begin
set @Risk = 'High'
end
if @RiskFactor > 0.0088
begin
set @Risk = 'Very High'
end
end
update spbirmingham set leads = @Leads, homes = @homes, risk_factor =
@RiskFactor, risk = @Risk where id = @id
FETCH NEXT FROM myCursor INTO @id, @postcode
end
end
close myCursor
deallocate myCursor

I tried the @@FETCH_STATUS <> -1 etc but this made no difference

[Hugo Kornelis]

Post by Mike
I have a table with 15000 records in it where I need to update a
couple of columns from a calculation. I would normally do this through
ADO and a bit of VB but I thought I would have a go at doing this
using a cursor in a stored procedure having never done it before. I
have followed the advice on the web but whenever I run the procedure
it gets between 100 and 1500 records into the table and then just
stops. The records have been imported from an Excel spread sheet.

(snip)

Hi Mike,

You didn't post enough information to reproduce the problem (see
www.aspfaq.com/5006 for what we need). However, I *THINK* that the
problem is related to using default cursor options, probably in
combination with there not being a proper key on the table. You might
find that the problem goes away if you explicitly ask for a STATIC
cursor.

That being said, you'll probably find that the performance of this thing
will be much better if you manage to rip out the cursor completely and
use a single, set-based update statement to work on the whole table at
once. With more information (see the link above), people in this group
are probably able to help you get there.

[Mike]

Many thanks for getting back to me, it turned out that the sp ran ok
it was just that it was timing out, extending the timeout period
solved the problem. But you are right, it ran really slowly and I will
investigate the alternative method you proposed.

Post by Hugo Kornelis

Post by Mike
I have a table with 15000 records in it where I need to update a
couple of columns from a calculation. I would normally do this through
ADO and a bit of VB but I thought I would have a go at doing this
using a cursor in a stored procedure having never done it before. I
have followed the advice on the web but whenever I run the procedure
it gets between 100 and 1500 records into the table and then just
stops. The records have been imported from an Excel spread sheet.

(snip)
Hi Mike,
You didn't post enough information to reproduce the problem (seewww.aspfaq.com/5006for what we need). However, I *THINK* that the
problem is related to using default cursor options, probably in
combination with there not being a proper key on the table. You might
find that the problem goes away if you explicitly ask for a STATIC
cursor.
That being said, you'll probably find that the performance of this thing
will be much better if you manage to rip out the cursor completely and
use a single, set-based update statement to work on the whole table at
once. With more information (see the link above), people in this group
are probably able to help you get there.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

[Adam Backlund]

Hi,
Shouldn't the last 'FETCH' statement be outside the 'BEGIN END' of the 'if (@@FETCH_STATUS <> -2)'?
It now looks like if the @@FETCH_STATUS would be 2, then there will be an eternal loop since the FETCH will never be executed anymore.
Am I right or have I missed something here?

Regards

Post by Hugo Kornelis
(snip)
Hi Mike,
You didn't post enough information to reproduce the problem (see
www.aspfaq.com/5006 for what we need). However, I *THINK* that the
problem is related to using default cursor options, probably in
combination with there not being a proper key on the table. You might
find that the problem goes away if you explicitly ask for a STATIC
cursor.
That being said, you'll probably find that the performance of this thing
will be much better if you manage to rip out the cursor completely and
use a single, set-based update statement to work on the whole table at
once. With more information (see the link above), people in this group
are probably able to help you get there.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post by Mike
I have a table with 15000 records in it where I need to update a
couple of columns from a calculation. I would normally do this through
ADO and a bit of VB but I thought I would have a go at doing this
using a cursor in a stored procedure having never done it before. I
have followed the advice on the web but whenever I run the procedure
it gets between 100 and 1500 records into the table and then just
stops. The records have been imported from an Excel spread sheet.
ALTER PROCEDURE BriminghamSP
AS
declare myCursor CURSOR FOR select id, postcode1 from SPBirmingham
open myCursor
begin
begin
begin
begin
end
begin
end
begin
end
begin
end
begin
end
end
@RiskFactor, risk = @Risk where id = @id
end
end
close myCursor
deallocate myCursor

Post by Mike
Many thanks for getting back to me, it turned out that the sp ran ok
it was just that it was timing out, extending the timeout period
solved the problem. But you are right, it ran really slowly and I will
investigate the alternative method you proposed.