@@FETCH_STATUS Problem

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(
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

On Tue, 3 Jun 2008 00:39:14 -0700 (PDT), Mike wrote:

>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:
(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