Hello,

I have two SP that are similar to the one below. When trying to run both of
them through Query analyzer the first one will execute properly but the
second one will say that it executed but doesn't really update any fields.
The only difference between the two is the name of the table that gets
updated and the number of tests. If I run either one independently through
there own query windows they both work.

TIA for any help.

CREATE  PROCEDURE [dbo].[sp_UpdateRF] AS

-- Update RF Grades


DECLARE @TestName VARCHAR(30)
DECLARE @TestGrade CHAR(10)
DECLARE @Employee_ID char(7)

DECLARE curRF CURSOR FOR SELECT TestName, TestGrade, Import.Employee_ID
FROM Import, RF
WHERE Import.Employee_ID = RF.Employee_ID
AND SUBSTRING(TestName, 1, 1)= '5'


OPEN curRF

WHILE @@FETCH_STATUS = 0

BEGIN
  FETCH NEXT FROM curRF INTO @TestName, @TestGrade, @Employee_ID

  IF SUBSTRING(@TestName, 3, 1) = '1'

   BEGIN
    UPDATE  RF
    SET RF.[1] = @TestGrade
    WHERE @Employee_ID = RF.Employee_ID
    AND RF.[1] IS NULL
   END

  IF SUBSTRING(@TestName, 3, 1) = '2'

   BEGIN
    UPDATE  RF
    SET RF.[2] = @TestGrade
    WHERE @Employee_ID = RF.Employee_ID
    AND RF.[2] IS NULL
   END

  IF SUBSTRING(@TestName, 3, 1) = '3'

   BEGIN
    UPDATE  RF
    SET RF.[3] = @TestGrade
    WHERE @Employee_ID = RF.Employee_ID
    AND RF.[3] IS NULL
   END

  IF SUBSTRING(@TestName, 3, 1) = '4'

   BEGIN
    UPDATE  RF
    SET RF.[4] = @TestGrade
    WHERE @Employee_ID = RF.Employee_ID
    AND RF.[4] IS NULL
   END
  IF SUBSTRING(@TestName, 3, 1) = '5'

   BEGIN
    UPDATE  RF
    SET RF.[5] = @TestGrade
    WHERE @Employee_ID = RF.Employee_ID
    AND RF.[5] IS NULL
   END

  IF SUBSTRING(@TestName, 3, 1) = '6'

   BEGIN
    UPDATE  RF
    SET RF.[6] = @TestGrade
    WHERE @Employee_ID = RF.Employee_ID
    AND RF.[6] IS NULL
   END



  IF SUBSTRING(@TestName, 3, 1) = 'F'
   BEGIN
    UPDATE RF
    SET RF.F = @TestGrade
    WHERE @Employee_ID = RF.Employee_ID
   END


END

CLOSE curRF
DEALLOCATE curRF


GO

On Thu, 25 Aug 2005 15:14:47 -0700, XImhotep wrote:

Hi XImhotep,

The problem is that you have some statements in the wrong order. You
should always have a FETCH _before_ testing @@FETCH_STATUS. If you don't
have a fetch before that, you'll end up testing the last fetch status of
the previously executed cursor.

The proper order of events is
DECLARE CURSOR
OPEN CURSOR
FETCH FIRST
WHILE @@FETCH_STATUS = 0
  BEGIN
  -- do something
  FETCH NEXT
  END
CLOSE CURSOR
DEALLOCATE CURSOR

However, most cursors are not needed at all. In 99% of the situations, a
set-based alternative will be faster, shorter, easier to read and hence
easier to maintain.

Your post doesn't reveal enough of your tables to make it worth an
attempt at rewriting it. However, if you post more information about
this, I'll be happy to have a look (and many others will too). See
www.aspfaq.com/5006 for an explanation of the information you should
provide if you seek help.

Best, Hugo

Thanks for the help. I missed the fetch statement at the bottom. Everyhting
is working now.

Thanks again.
Show quoteHide quote

XImhotep wrote:
Try adding some debug code to the procedures and see if they are both
running the updates. Also, how are you executing the two procedures? Do
you have two EXEC statements in the same QA window that are being
executed as a batch?

Return only the Third Row
Choosing one record over another
can't return zero when no rows exist using aggregate function sum
Table name as a variable
Count CHAR(11) in a string