Problem running multiple SP's

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
GOOn Thu, 25 Aug 2005 15:14:47 -0700, XImhotep wrote:

>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.
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 s help.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||XImhotep wrote:
> 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
> <SNIP>
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?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for the help. I missed the fetch statement at the bottom. Everyhting
is working now.
Thanks again.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:08isg1lm0qc9s5f8001gd2relr27k9s568@.
4ax.com...
> 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 s help.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)