?
Solved

SQL @@Fetch next from - problem not returning all records

Posted on 2005-05-08
6
Medium Priority
?
388 Views
Last Modified: 2008-03-03
I have a simple issue where the number of rows are not being returned correctly. The are 3 records I know should be returned from this procedure as it works with a simple Select statement, but when i use it in a SP, it returns 2 unique records and the third is a duplicate of the 2nd? All i want to do is return the correct number of records as these are emailed to the me. Here is the sample SP code. Cheers

-----------------------------------------------------------------------------------------------------------
CREATE PROCEDURE cb_Test_get_new_PGroup_21

 AS

DECLARE @frmemail varchar (100)
DECLARE @toemail varchar (100)
DECLARE @SenderAddress varchar(100)
DECLARE @RecipientAddress varchar(100)
DECLARE @Subject varchar(200)
DECLARE @Body varchar(8000)
DECLARE @oMail int --Object reference
DECLARE @resultcode int

SET @SenderAddress= 'john.miles@internetexpress.net.au'
SET @RecipientAddress = 'john.miles@internetexpress.net.au'
SELECT @Subject = 'ComputerBuy New Product Group Added  ' + CAST(getdate() AS
varchar(12))

SET  @Body = 'New product group 2 category codes have been added to the catalogue by Ingram<br><br>

Please action these new codes against your client pricing tables.<br><br> '

DECLARE @prodgroup2 varchar(70),@pgd1 varchar(80), @pgd2_prodedtl varchar(70),@pg2_CB varchar (80),@pgc1_prodedtl varchar (80)

DECLARE get_pg2s CURSOR FOR

SELECT DISTINCT
PRODEDTL.product_group_code_2 AS IM_PG2, PRODEDTL.product_group_description_1, PRODEDTL.product_group_description_2,
CB_PCT_ClientsTest.product_group_code_2, PRODEDTL.product_group_code_1
FROM         PRODEDTL LEFT OUTER JOIN
CB_PCT_ClientsTest ON PRODEDTL.product_group_code_2 = CB_PCT_ClientsTest.product_group_code_2
WHERE     (CB_PCT_ClientsTest.product_group_code_2 IS NULL)
ORDER BY PRODEDTL.product_group_description_2

OPEN get_pg2s
Begin
FETCH NEXT FROM get_pg2s Into @prodgroup2 ,@pgd1 ,@pgd2_prodedtl ,@pg2_CB,@pgc1_prodedtl
End
WHILE @@FETCH_STATUS = 0

Begin

/*get the whole record inf id*/

FETCH NEXT FROM get_pg2s Into @prodgroup2 ,@pgd1 ,@pgd2_prodedtl ,@pg2_CB,@pgc1_prodedtl

SET  @Body = @body  +  CAST(@pgc1_prodedtl  AS varchar(70)) + '       |      ' +  CAST(@prodgroup2 AS varchar(70)) + '       |      ' + CAST(@pgd2_prodedtl  AS varchar(70)) + '   |  '  +  CAST(@pgd1 AS varchar(70)) + '<br>'

End

SET @Body = @body +  ' <br><br> Contact John for more assistance.'

EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
IF @resultcode = 0
BEGIN
   EXEC @resultcode = sp_OASetProperty @oMail, 'BodyFormat', 0
   EXEC @resultcode = sp_OASetProperty @oMail, 'MailFormat', 0
   EXEC @resultcode = sp_OASetProperty @oMail, 'Importance', 1
   EXEC @resultcode = sp_OASetProperty @oMail, 'From',
@SenderAddress
   EXEC @resultcode = sp_OASetProperty @oMail, 'To',
@RecipientAddress
   EXEC @resultcode = sp_OASetProperty @oMail, 'Subject',
@Subject
   EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
   EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
   EXEC sp_OADestroy @oMail
END

/*close the user loop*/
CLOSE get_pg2s
/*kill the loop cursor*/
DEALLOCATE get_pg2s
GO
0
Comment
Question by:jonmiles
  • 3
  • 2
6 Comments
 

Author Comment

by:jonmiles
ID: 13957320
Sorry - this is the output I get in an email:

New product group 2 category codes have been added to the catalogue by Ingram

Please action these new codes against your client pricing tables.

140 | 1400022 | Sound cards | Audio/Visual
160 | 1600050 | Video Cards | Components
160 | 1600050 | Video Cards | Components

'the last entry should be 160 | 1600070 | Graphic_Controllers | Components"
for some reason it is not writing the last 1600070 code

0
 
LVL 8

Expert Comment

by:Julianva
ID: 13957788
Please try to

Use inner join instead of outer joins


inner join will give you only records that match while
other join will give you all the records - including
duplicates.
0
 

Author Comment

by:jonmiles
ID: 13957927
the information I need being returned is correct in so far as i need to use outer jons to bring back ONLY the data that is new in the query. if I use a simple Select statement then I get the 3 unique rows returned, when I use the Cursor I get 2 distinct rows and the 3rd one is a repeat of the 2nd last row - a duplicate. thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Julianva
ID: 13958035
try this as a suggestion

CREATE PROCEDURE cb_Test_get_new_PGroup_21

 AS

DECLARE @frmemail varchar (100)
DECLARE @toemail varchar (100)
DECLARE @SenderAddress varchar(100)
DECLARE @RecipientAddress varchar(100)
DECLARE @Subject varchar(200)
DECLARE @Body varchar(8000)
DECLARE @oMail int --Object reference
DECLARE @resultcode int

SET @SenderAddress= 'john.miles@internetexpress.net.au'
SET @RecipientAddress = 'john.miles@internetexpress.net.au'
SELECT @Subject = 'ComputerBuy New Product Group Added  ' + CAST(getdate() AS
varchar(12))

SET  @Body = 'New product group 2 category codes have been added to the catalogue by Ingram<br><br>

Please action these new codes against your client pricing tables.<br><br> '

DECLARE @prodgroup2 varchar(70),@pgd1 varchar(80), @pgd2_prodedtl varchar(70),@pg2_CB varchar (80),@pgc1_prodedtl varchar (80)

DECLARE get_pg2s CURSOR FOR

SELECT DISTINCT
PRODEDTL.product_group_code_2 AS IM_PG2, PRODEDTL.product_group_description_1, PRODEDTL.product_group_description_2,
CB_PCT_ClientsTest.product_group_code_2, PRODEDTL.product_group_code_1
FROM         PRODEDTL LEFT OUTER JOIN
CB_PCT_ClientsTest ON PRODEDTL.product_group_code_2 = CB_PCT_ClientsTest.product_group_code_2
WHERE     (CB_PCT_ClientsTest.product_group_code_2 IS NULL)
ORDER BY PRODEDTL.product_group_description_2

OPEN get_pg2s

FETCH NEXT FROM get_pg2s Into @prodgroup2 ,@pgd1 ,@pgd2_prodedtl ,@pg2_CB,@pgc1_prodedtl

WHILE @@FETCH_STATUS = 0

Begin
SET  @Body = @body  +  CAST(@pgc1_prodedtl  AS varchar(70)) + '       |      ' +  CAST(@prodgroup2 AS varchar(70)) + '       |      ' + CAST(@pgd2_prodedtl  AS varchar(70)) + '   |  '  +  CAST(@pgd1 AS varchar(70)) + '<br>'

SET @Body = @body +  ' <br><br> Contact John for more assistance.'

EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
IF @resultcode = 0
BEGIN
   EXEC @resultcode = sp_OASetProperty @oMail, 'BodyFormat', 0
   EXEC @resultcode = sp_OASetProperty @oMail, 'MailFormat', 0
   EXEC @resultcode = sp_OASetProperty @oMail, 'Importance', 1
   EXEC @resultcode = sp_OASetProperty @oMail, 'From',
@SenderAddress
   EXEC @resultcode = sp_OASetProperty @oMail, 'To',
@RecipientAddress
   EXEC @resultcode = sp_OASetProperty @oMail, 'Subject',
@Subject
   EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
   EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
   EXEC sp_OADestroy @oMail
END

FETCH NEXT FROM get_pg2s Into @prodgroup2 ,@pgd1 ,@pgd2_prodedtl ,@pg2_CB,@pgc1_prodedtl


CLOSE get_pg2s
DEALLOCATE get_pg2s

all i have done is rearrange the begin and end statments and put the second fetch statment at the bottom - which means that if the first fetch succeeds then continue










0
 
LVL 14

Accepted Solution

by:
Renante Entera earned 500 total points
ID: 13958041
Hi jonmiles!

Your procedure needs to be revised.

Here's the revised script :

CREATE PROCEDURE cb_Test_get_new_PGroup_21

 AS

DECLARE @frmemail varchar (100)
DECLARE @toemail varchar (100)
DECLARE @SenderAddress varchar(100)
DECLARE @RecipientAddress varchar(100)
DECLARE @Subject varchar(200)
DECLARE @Body varchar(8000)
DECLARE @oMail int --Object reference
DECLARE @resultcode int

SET @SenderAddress= 'john.miles@internetexpress.net.au'
SET @RecipientAddress = 'john.miles@internetexpress.net.au'
SELECT @Subject = 'ComputerBuy New Product Group Added  ' + CAST(getdate() AS
varchar(12))

SET  @Body = 'New product group 2 category codes have been added to the catalogue by Ingram<br><br>

Please action these new codes against your client pricing tables.<br><br> '

DECLARE @prodgroup2 varchar(70),@pgd1 varchar(80), @pgd2_prodedtl varchar(70),@pg2_CB varchar (80),@pgc1_prodedtl varchar (80)

DECLARE get_pg2s CURSOR FOR

SELECT DISTINCT
PRODEDTL.product_group_code_2 AS IM_PG2, PRODEDTL.product_group_description_1, PRODEDTL.product_group_description_2,
CB_PCT_ClientsTest.product_group_code_2, PRODEDTL.product_group_code_1
FROM         PRODEDTL LEFT OUTER JOIN
CB_PCT_ClientsTest ON PRODEDTL.product_group_code_2 = CB_PCT_ClientsTest.product_group_code_2
WHERE     (CB_PCT_ClientsTest.product_group_code_2 IS NULL)
ORDER BY PRODEDTL.product_group_description_2

OPEN get_pg2s
FETCH NEXT FROM get_pg2s Into @prodgroup2 ,@pgd1 ,@pgd2_prodedtl ,@pg2_CB,@pgc1_prodedtl

WHILE @@FETCH_STATUS = 0
Begin
SET  @Body = @body  +  CAST(@pgc1_prodedtl  AS varchar(70)) + '       |      ' +  CAST(@prodgroup2 AS varchar(70)) + '       |      ' + CAST(@pgd2_prodedtl  AS varchar(70)) + '   |  '  +  CAST(@pgd1 AS varchar(70)) + '<br>'
/*get the whole record inf id*/
FETCH NEXT FROM get_pg2s Into @prodgroup2 ,@pgd1 ,@pgd2_prodedtl ,@pg2_CB,@pgc1_prodedtl
End

SET @Body = @body +  ' <br><br> Contact John for more assistance.'

EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
IF @resultcode = 0
BEGIN
   EXEC @resultcode = sp_OASetProperty @oMail, 'BodyFormat', 0
   EXEC @resultcode = sp_OASetProperty @oMail, 'MailFormat', 0
   EXEC @resultcode = sp_OASetProperty @oMail, 'Importance', 1
   EXEC @resultcode = sp_OASetProperty @oMail, 'From',
@SenderAddress
   EXEC @resultcode = sp_OASetProperty @oMail, 'To',
@RecipientAddress
   EXEC @resultcode = sp_OASetProperty @oMail, 'Subject',
@Subject
   EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
   EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
   EXEC sp_OADestroy @oMail
END

/*close the user loop*/
CLOSE get_pg2s
/*kill the loop cursor*/
DEALLOCATE get_pg2s
GO

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
LVL 8

Expert Comment

by:Julianva
ID: 13958065
please include the end after the word assistance.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question