Link to home
Start Free TrialLog in
Avatar of jonmiles
jonmiles

asked on

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

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
Avatar of jonmiles
jonmiles

ASKER

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

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










ASKER CERTIFIED SOLUTION
Avatar of Renante Entera
Renante Entera
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
please include the end after the word assistance.