Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

WHILE not working properly 1

Status
Not open for further replies.
dnfrantum

dnfrantum

Programmer
Oct 23, 2001
175
US
I am attempting to send out 72 separate emails using the following code;
Code: 
DECLARE @cardname varchar(100),
        @mailrecipients varchar(100),
        @numatcard varchar(100),
        @trackno varchar(50),
        @docnum as varchar(50),
        @DocDate as varchar(50),
        @newBody nvarchar(max),
        @subject1 varchar(100)

SELECT T1.E_mail,T0.CardName,T0.NumAtCard,ISNULL(T0.TrackNo,'1ZOOPS') AS TrackNo,T0.DocDate,T0.DocNum, 0 as processed
INTO #maillist
FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.SlpCode = 3 AND T0.NumAtCard is not null AND T0.Series = 37 AND T0.DocDate > getdate()-2

WHILE (select count(*) from #maillist where processed = 0) > 0

BEGIN
SELECT TOP 1 @cardname = isnull(CardName,''),
             @numatcard = isnull(NumAtCard,''),
             @trackno = isnull(TrackNo,''),
             @mailrecipients = isnull(E_mail,''),
             @docnum = DocNum,
             @docdate = substring(CAST(DocDate as NVARCHAR(11)),1,11)
FROM #maillist
WHERE processed = 0

SET @newBody = '
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Your return has been processed</title>
<style type="text/css">
<!--
.callout {
	border-width:0;vertical-align:baseline;background-color:transparent;display:inline;float:left;position:relative;width:800px;margin-top:0;margin-bottom:0;margin-right:auto;margin-left:0px;text-align:center;padding-top:15px;padding-bottom:0;padding-right:0;padding-left:0;background-image:url([URL unfurl="true"]http://www.goorin.com/sites/default/files/templates/orderproc/promo_lines.gif);background-repeat:no-repeat;height:35px;font-family:Georgia,Trebuchet[/URL] MS,Times New Roman;font-size:100%;font-weight:bold;color:#682609; text-transform: capitalize;	
}
.action {
	margin-right:0; margin-left:0; padding-top:0; padding-bottom:0; padding-right:0; padding-left:0; border-width:0; font-size:100%; vertical-align:baseline; background-color:transparent; line-height:1.5em; letter-spacing:1.1px;" ><span style="margin-top:0;margin-bottom:0;margin-right:0;margin-left:0;padding-top:0;padding-bottom:0;padding-right:0;padding-left:0;border-width:0;vertical-align:baseline;background-color:transparent;font-size:20px;color:#682609;
}

p {
	font-family:Georgia, "Times New Roman", Times, serif;
}

.invoicedetails {
	font-family:Georgia, "Times New Roman", Times, serif;
	font-size: 14px;
}
-->
</style>
</head>
<body>
<table background="[URL unfurl="true"]http://www.goorin.com/sites/default/files/templates/orderproc/bg_repeat_42.jpg">[/URL]
<tr>
<td align="center">
  
  <table width="800">
    <tr>
      <td><a href="[URL unfurl="true"]http://www.goorin.com"><img[/URL] src="[URL unfurl="true"]http://www.goorin.com/sites/default/files/templates/orderproc/bg_header.gif"[/URL] border="0"/></a></td>
      </tr>
    <tr>
      <td><table style="width:800px;border-width:0;padding-top:0;padding-bottom:0;padding-right:0;padding-left:0;border-width:0;position:relative;float:left;background-image:url([URL unfurl="true"]http://www.goorin.com/sites/default/files/templates/orderproc/bg_nav.jpg);background-repeat:no-repeat;background-position:center;background-attachment:scroll;height:30px;"><tr><td>[/URL]
        <div style="font-family:Georgia,Trebuchet MS,Times New Roman;font-size:16px;color:#000;margin-top:5px;padding:0px; height:33px; vertical-align:middle;"> <span style="margin-left:20px; margin-right:20px;"><a style="color:black;text-decoration:none;" href="[URL unfurl="true"]http://www.goorin.com/hats">Shop</a></span>[/URL] <span style="margin-right:20px;"><a style="color:black;text-decoration:none;" href="[URL unfurl="true"]http://www.goorin.com/blog">Blog</a></span>[/URL] <span style="margin-right:20px;"><a style="color:black;text-decoration:none;" href="[URL unfurl="true"]http://www.goorin.com/about-goorin">The[/URL] Goorin Story</a></span> <span style="margin-right:20px;"><a style="color:black;text-decoration:none;" href="[URL unfurl="true"]http://www.goorin.com/hat-shops">Our[/URL] Hat Shops</a></span> <span style="margin-right:20px;"><a style="color:black;text-decoration:none;" href="[URL unfurl="true"]http://www.goorin.com/faq">Customer[/URL] Service</a></span> </div>
        </td></tr></table></td>
      </tr>
    <tr>
      <td class="callout">SHIPPING CONFIRMATION</td>
      </tr>
    <tr>
      <td><table width="800">
        <tr>
          <td width="530" align="left" valign="top"><p class="action">
            Your order has shipped. </p>
            
  <p style="font-family:Georgia,Trebuchet MS,Times New Roman;">You''re just a few days away from looking (even more) stylish in your new Goorin hat.<br /><br />
    
    Now that you''ve ordered with us, keep up to date on the latest Goorin happenings and deals via our <a href="http:/[URL unfurl="true"]www.goorin.com/blog">blog</a>,[/URL] our <a href="[URL unfurl="true"]http://www.facebook.com/goorinbrothers">Facebook[/URL] Page</a>, and our <a href="[URL unfurl="true"]http://www.twitter.com/goorinbros">Twitter[/URL] stream</a>.<br /><br />
    
    When you receive your order, take a photo of yourself in your new hat and publish it to our <a href="[URL unfurl="true"]http://www.facebook.com/goorinbrothers">Facebook[/URL] wall</a>  to receive 15% off your next purchase.</p></td>
          <td>&nbsp;</td>
          <td><p><img src="[URL unfurl="true"]http://www.goorin.com/sites/default/files/templates/orderproc/400x400.jpg"[/URL] alt="" width="400" height="300" /></p>
            <p><em>Mr. Fitzall''s on his way with your new Goorin hat!</em></p></td>
          </tr>
        </table></td>
      </tr>
    <tr>
      <td class="callout">INVOICE DETAILS
        </td>
      </tr>
    <tr>
      <td align="center">
        <table border="0" cellpadding="2" cellspacing="0" width="400">
          
          <tr>
            <td valign="top">
              
              <table border="0" cellpadding="2" cellspacing="1" width="100%" style="border-collapse: collapse; border-color:#d3d3d3;">
                <tr>
                  <td bgcolor="#D8D8D8" valign="top" class="invoicedetails">Order #
                    </td>
                  <td bgcolor="#FFFFFF" valign="top" class="invoicedetails">' + @numatcard + '</td>
                  </tr>
                
                <tr>
                  <td bgcolor="#D8D8D8" valign="top" class="invoicedetails">Reference #
                    </td>

                  <td bgcolor="#FFFFFF" valign="top" class="invoicedetails">' + @docnum + '</td>
                  </tr>
                
                <tr>
                  <td bgcolor="#D8D8D8" valign="top" class="invoicedetails">Order shipped
                    </td>

                  <td bgcolor="#FFFFFF" valign="top" class="invoicedetails">' + @docdate + '</td>
                  </tr>
                <tr>
                  <td bgcolor="#D8D8D8" valign="top" class="invoicedetails">Ship Method</td>
                  <td bgcolor="#FFFFFF" valign="top" class="invoicedetails">UPS Ground</td>
                </tr>
                <tr>
                  <td bgcolor="#D8D8D8" valign="top" class="invoicedetails">Tracking number</td>
                  <td bgcolor="#FFFFFF" valign="top" class="invoicedetails">' + @trackno + '</td>
                </tr>
                
                </table>
              </td>
            </tr>
  </table>
  </td>
  </tr>
  <tr>
  <td class="callout">
    QUESTIONS FEEDBACK</td>
  </tr>
  <tr>
  <td>
  <p>Don''t like your hat? Is it the wrong fit? </p>
  </td>
  </tr>
  </table>
  
</td>
</table>'

SET @subject1 = 'Your Goorin Brothers Order: ' + @numatcard + ' is on its way!'
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MyMailProfile',
    @recipients = @mailrecipients,    
    @subject = @subject1, 
    @body = @newBody,
    @body_format = 'HTML';


UPDATE #maillist set processed = 1 where CardName = @cardname
END;

SELECT * FROM #maillist;

DROP TABLE #maillist;

However, while the temp table returns 72 records, I only get one email. I am sure it is just the placement of my statements or the ommission of an 'END', but I can't figure it out. Could someone offer some advice or just a pair of fresh eyes?

Thanks in advance,
Donald
 
Sort by date Sort by votes
Don't see a problem.

Have you tried the same code but without sending an e-mail, just print something instead (and also you may comment out extra code) - just to simplify.

PluralSight Learning Library
 
Upvote 0 Downvote
Same thing, it only prints one record. Should there be a 'Next' after the 'END' statement?

Thanks in advance,
Donald
 
Upvote 0 Downvote
At the end, just before the loop ends, you have this:

[tt]UPDATE #maillist set processed = 1 where CardName = @cardname[/tt]

Is it possible that this is updating all the rows, setting them to processed = 1. This would account for just one pass through the loop.

Personally, I've never been a big fan of this looping method. What I would have done instead is...

I would add an integer identity column to the #maillist temporary table. Then I would have used that value to control the loop. Something like this...

[tt][blue]
Create Table #maillist(RowId Int Identity(1,1), Your Other Columns Here)....

Insert Into #MailList(All columns except the RowId)
Select *All Your Existing Columns*
From WhateverTable

Declare @i Int, @Max Int

Select @i = 1, @Max = Max(RowId) From #maillist

While @i <= @Max
Begin
Select @cardname = isnull(cardname, ''),
*Other Columns*
From #maillist
Where RowId = @i

-- Do your emailing stuff here

Set @i = @i + 1
End
[/blue][/tt]

The reason I don't like using counts to control the loop is because it's too easy to get yourself in to an endless loop. With the pseudocode I present, it's less likely to make a mistake, and nearly impossible to get an endless loop.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
That was it, thanks. BTW, I will include you code as well. Thanks for all of your help. I knew it was something small.

Thanks in advance,
Donald
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Working Days giving NULL values
Replies
8
Views
305
Cpreston
Cpreston
Lidias13
  • Locked
  • Question
Issue trying to find &quot;closest&quot; dated record to a rather complex query (not working)
Replies
1
Views
165
ggriffit
ggriffit
Cpreston
  • Locked
  • Question
Will not show row with Line Type 2 1
Replies
17
Views
338
Cpreston
Cpreston
ahmedsa2018
  • Locked
  • Question
How to give same number if it exist on table or new number if it not exist based on part value? 1
Replies
8
Views
218
Andrzejek
Andrzejek
03Explorer
  • Locked
  • Question
sproc While Exist loop through recordset 1
Replies
2
Views
120
03Explorer
03Explorer

Part and Inventory Search

Sponsor

Back
Top