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@internetexpres s.net.au'
SET @RecipientAddress = 'john.miles@internetexpres s.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_cod e_2 AS IM_PG2, PRODEDTL.product_group_des cription_1 , PRODEDTL.product_group_des cription_2 ,
CB_PCT_ClientsTest.product _group_cod e_2, PRODEDTL.product_group_cod e_1
FROM PRODEDTL LEFT OUTER JOIN
CB_PCT_ClientsTest ON PRODEDTL.product_group_cod e_2 = CB_PCT_ClientsTest.product _group_cod e_2
WHERE (CB_PCT_ClientsTest.produc t_group_co de_2 IS NULL)
ORDER BY PRODEDTL.product_group_des cription_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
--------------------------
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@internetexpres
SET @RecipientAddress = 'john.miles@internetexpres
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_cod
CB_PCT_ClientsTest.product
FROM PRODEDTL LEFT OUTER JOIN
CB_PCT_ClientsTest ON PRODEDTL.product_group_cod
WHERE (CB_PCT_ClientsTest.produc
ORDER BY PRODEDTL.product_group_des
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
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.
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.
ASKER
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@internetexpres s.net.au'
SET @RecipientAddress = 'john.miles@internetexpres s.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_cod e_2 AS IM_PG2, PRODEDTL.product_group_des cription_1 , PRODEDTL.product_group_des cription_2 ,
CB_PCT_ClientsTest.product _group_cod e_2, PRODEDTL.product_group_cod e_1
FROM PRODEDTL LEFT OUTER JOIN
CB_PCT_ClientsTest ON PRODEDTL.product_group_cod e_2 = CB_PCT_ClientsTest.product _group_cod e_2
WHERE (CB_PCT_ClientsTest.produc t_group_co de_2 IS NULL)
ORDER BY PRODEDTL.product_group_des cription_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
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@internetexpres
SET @RecipientAddress = 'john.miles@internetexpres
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_cod
CB_PCT_ClientsTest.product
FROM PRODEDTL LEFT OUTER JOIN
CB_PCT_ClientsTest ON PRODEDTL.product_group_cod
WHERE (CB_PCT_ClientsTest.produc
ORDER BY PRODEDTL.product_group_des
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
please include the end after the word assistance.
ASKER
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