Loop within a loop... BUT...

Question

Post reply

Loop within a loop... BUT...

donato1026

SSCrazy

Points: 2765
More actions
October 4, 2011 at 7:43 am

#146125

Good morning all,
I have a look-up table with some very basic fields. One of them is a customer code (cust_cd). I have an entry or row for each item they have ordered. I want to send ONE (1) email to each customer, however, within that email, I want to list each item in a table format. I am not sure how to go about this...
Thank you VERY much!
~D

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You can use Database Mail. sp_send_dbmail can send query results as an attachment or inline e-mail body.

If you share more details, we could try to help.

-- Gianluca Sartori

donato1026 SSCrazy Points: 2765 More actions · Answer 2

Gianluca Sartori (10/4/2011)
You can use Database Mail. sp_send_dbmail can send query results as an attachment or inline e-mail body.
If you share more details, we could try to help.

Thank you very much... I know to use database mail. I already have plenty of automated emails that do what I need them to do. This is the first email, however, that will need to iterate through one look-up table to get lines per customer. I am not sure how much detail I would be able to give. It is one table. Very basic look-up table. The only thing that is not basic (to me at least) is looping through that table within a loop and getting each line itwm grouped by cust_cd. But I only want to send ONE (1) email per customer, with all of their line items in the email. This is coming from a single table though...

Thanks again!

~D

donato1026 SSCrazy Points: 2765 More actions · Answer 3

donato1026

SSCrazy

Points: 2765

October 4, 2011 at 8:36 am

#1390171

And I see you are a fellow Italian... Gratzi.

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Can you post some sample data, so that we can try to set up some code for you?

Read the first article linked in my signature line and you will find out how to post sample data.

Thanks

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

donato1026 (10/4/2011)
And I see you are a fellow Italian... Gratzi.

Non c'è di che 😀

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 6

i'm guessing here, but i'm thinking that using a FOR XML to concatenate the itwm field into a comma delimited list might be what you are after;

here's a basic example for you to look at...then you could make the MailBody have the concatenated list,a dn the customer id?

declare @skills table (Resource_Id int, Skill_Id varchar(20))

insert into @skills

select 101, 'sqlserver' union all

select 101, 'vb.net' union all

select 101, 'oracle' union all

select 102, 'sqlserver' union all

select 102, 'java' union all

select 102, 'excel' union all

select 103, 'vb.net' union all

select 103, 'java' union all

select 103, 'oracle'

---

--select * from @skills s1

--- Concatenated Format

set statistics time on;

SELECT Resource_Id,stuff(( SELECT ',' + Skill_Id

FROM @skills s2

WHERE s2.Resource_Id= s1.resource_ID --- must match GROUP BY below

ORDER BY Skill_Id

FOR XML PATH('')

),1,1,'') as [Skills]

FROM @skills s1

--WHERE Resource_id = 101

GROUP BY s1.Resource_Id --- without GROUP BY multiple rows are returned

ORDER BY s1.Resource_Id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

donato1026 SSCrazy Points: 2765 More actions · Answer 7

Gianluca Sartori (10/4/2011)
donato1026 (10/4/2011)
And I see you are a fellow Italian... Gratzi.
Non c'è di che 😀

Ok. Here is the table:

USE [DatabaseName]

GO

/****** Object: Table [dbo].[EMAIL_TABLE_TEST] Script Date: 10/04/2011 15:53:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[EMAIL_TABLE_TEST](

[DEL_DOC_NUM] [varchar](30) NULL,

[MAX_DUE_DATE] [datetime] NULL,

[INSERT_DATE] [datetime] NULL,

[BO_SKU_COUNT] [int] NULL,

[varchar](60) NULL,

[CUST_CD] [varchar](10) NULL,

[SHIP_TO_F_NAME] [varchar](15) NULL,

[SHIP_TO_L_NAME] [varchar](20) NULL,

[SHIP_TO_ADDR1] [varchar](30) NULL,

[SHIP_TO_ADDR2] [varchar](30) NULL,

[SHIP_TO_CITY] [varchar](20) NULL,

[SHIP_TO_ST_CD] [varchar](2) NULL,

[SHIP_TO_ZIP_CD] [varchar](10) NULL,

[SHIP_TO_H_PHONE] [varchar](12) NULL,

[FNAME] [varchar](15) NULL,

[LNAME] [varchar](20) NULL,

[ADDR1] [varchar](30) NULL,

[ADDR2] [varchar](30) NULL,

[CITY] [varchar](20) NULL,

[ST_CD] [varchar](2) NULL,

[ZIP_CD] [varchar](10) NULL,

[HOME_PHONE] [varchar](12) NULL,

[AVAIL_TEXT] [nvarchar](36) NULL,

[ITM_CD] [varchar](9) NULL,

[VSN] [varchar](30) NULL,

[DES] [varchar](30) NULL,

[DEL_DOC_LN#] [numeric](4, 0) NULL,

[QTY] [int] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

donato1026 SSCrazy Points: 2765 More actions · Answer 8

And here is some test data:

INSERT INTO EMAIL_TABLE_TEST

(DEL_DOC_NUM, MAX_DUE_DATE, INSERT_DATE, BO_SKU_COUNT, EMAIL, CUST_CD,

SHIP_TO_F_NAME, SHIP_TO_L_NAME, SHIP_TO_ADDR1, SHIP_TO_ADDR2, SHIP_TO_CITY,

SHIP_TO_ST_CD, SHIP_TO_ZIP_CD, SHIP_TO_H_PHONE, FNAME, LNAME, ADDR1, ADDR2,

CITY, ST_CD, ZIP_CD, HOME_PHONE, AVAIL_TEXT, ITM_CD, VSN, DES, DEL_DOC_LN#, QTY)

SELECT '0903118NKTQ','2011-09-30 00:00:00.000','2011-09-23 00:00:00.000',2,'donato1026@yahoo.com','ARMSJ59806','JULIE','ARMSTRONG','8322 SHORTHORN DR',NULL,'NORTHFIELD','OH','44067','330-467-2618','JULIE','ARMSTRONG','8322 SHORTHORN DR',NULL,'NORTHFIELD','OH','44067','330-467-2618','Late September','3399020','BRY,3399-02','END TABLE',2,2 UNION ALL

SELECT '0903118NKTQ','2011-09-30 00:00:00.000','2011-09-23 00:00:00.000',2,'donato1026@yahoo.com','ARMSJ59806','JULIE','ARMSTRONG','8322 SHORTHORN DR',NULL,'NORTHFIELD','OH','44067','555-555-5555','JULIE','ARMSTRONG','8322 SHORTHORN DR',NULL,'NORTHFIELD','OH','44067','555-555-5555','Late September','3399020','BRY,3399-02','END TABLE',2,2 UNION ALL

SELECT '0903118NKTQ','2011-09-30 00:00:00.000','2011-09-23 00:00:00.000',2,'donato1026@yahoo.com','ARMSJ59807','JULIE','ARMSTRONG','8322 SHORTHORN DR',NULL,'NORTHFIELD','OH','44067','555-555-5555','JULIE','ARMSTRONG','8322 SHORTHORN DR',NULL,'NORTHFIELD','OH','44067','555-555-5555','Late September','3399020','BRY,3399-02','END TABLE',2,2 UNION ALL

SELECT '0903118NKTQ','2011-09-30 00:00:00.000','2011-09-23 00:00:00.000',2,'donato1026@yahoo.com','ARMSJ59807','JULIE','ARMSTRONG','8322 SHORTHORN DR',NULL,'NORTHFIELD','OH','44067','555-555-5555','JULIE','ARMSTRONG','8322 SHORTHORN DR',NULL,'NORTHFIELD','OH','44067','555-555-5555','Late September','3399020','BRY,3399-02','END TABLE',2,2

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

OK, and what is supposed to go into the e-mail body?

All the columns? If so, I think that the XML solution is not feasible: you would have to convert and concatenate all the columns and use char(10) as line separator. A nightmare.

I think your best bet is a c-u-r-s-o-r on DISTINCT customer and invoke sp_send_dbmail passing a query parametrized with the current customer id.

-- Gianluca Sartori

donato1026 SSCrazy Points: 2765 More actions · Answer 10

Gianluca Sartori (10/4/2011)
OK, and what is supposed to go into the e-mail body?
All the columns? If so, I think that the XML solution is not feasible: you would have to convert and concatenate all the columns and use char(10) as line separator. A nightmare.
I think your best bet is a c-u-r-s-o-r on DISTINCT customer and invoke sp_send_dbmail passing a query parametrized with the current customer id.

What I need to do is send one email per cust_cd... but within that email, I would like to send each line item they may have for their order - letting them know when it will be coming in, say Early November, Mid November or Late November.

As an example:

Dear John Smith,

Thank you for your recent purchase with us. We are sending this email to inform you that the following items will be arriving LATE NOVEMBER:

SKU VSN DES

If you have any questions, please feel free to contact us at [email_address].

Thank you,

Company Name

donato1026 SSCrazy Points: 2765 More actions · Answer 11

Here is something that I wrote for our delivery emails... I need something like this, however, like I had said before, I want to only send one email to the customer, with their line items listed in the email body.

Thanks again!

USE [DatabaseName]

GO

/****** Object: StoredProcedure [dbo].[usp_send_delivery_emails] Script Date: 10/05/2011 09:46:25 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:Dan

-- Created: 03/03/2011

-- Description:This procedure will loop through the ASAP_EMAIL_LIST table and retrieve a list of customers.

-- From there, it will send an email to each customer's email addresses letting them know that their order has been

-- received and filled here in our warehouse and it is ready for delivery.

-- The email will have a link for them to visit on our website where they will be brought to a form to schedule their delivery.

--

-- =============================================

ALTER PROC [dbo].[usp_send_delivery_emails]

AS

DECLARE

@ship_to_f_name nvarchar(MAX)

,@ship_to_l_name nvarchar(MAX)

,@ship_to_addr1 nvarchar(MAX)

,@ShippingAddress nvarchar(MAX)

,@ship_to_addr2 nvarchar(MAX)

,@ship_to_city nvarchar(MAX)

,@ship_to_st_cd nvarchar(MAX)

,@ship_to_zip_cd nvarchar(MAX)

,@ship_to_h_phone nvarchar(MAX)

,@bill_to_f_name nvarchar(MAX)

,@bill_to_l_name nvarchar(MAX)

,@bill_to_addr1 nvarchar(MAX)

,@BillingAddress nvarchar(MAX)

,@bill_to_addr2 nvarchar(MAX)

,@bill_to_city nvarchar(MAX)

,@bill_to_st_cd nvarchar(MAX)

,@bill_to_zip_cd nvarchar(MAX)

,@bill_to_h_phone nvarchar(MAX)

,@cust_cd nvarchar(MAX)

,@email nvarchar(MAX)

,@body nvarchar(MAX);

DECLARE Contacts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

SELECT

distinct(cust_cd),

ship_to_f_name,

ship_to_l_name,

coalesce(

ship_to_addr1 + char(13) + char(10) + ship_to_addr2,

ship_to_addr1,

ship_to_addr2,

'') as ShippingAddress,

--ship_to_addr1,

--ship_to_addr2,

--isnull(ship_to_addr2, ''),

ship_to_city,

ship_to_st_cd,

ship_to_zip_cd,

ship_to_h_phone,

bill_to_f_name,

bill_to_l_name,

coalesce(

bill_to_addr1 + char(13) + char(10) + bill_to_addr2,

bill_to_addr1,

bill_to_addr2,

'') as BillingAddress,

--bill_to_addr1,

--bill_to_addr2,

--isnull(bill_to_addr2, ''),

bill_to_city,

bill_to_st_cd,

bill_to_zip_cd,

bill_to_h_phone,

email

FROM dbo.ASAP_EMAIL_LIST

where

convert(varchar(10), date_email, 101) = CONVERT(VARCHAR(10), GETDATE(), 101)

AND pu_del = 'D' and open_del_date is not null

AND EmailSent is null

AND dbo.emailFilter(email) = 0

AND (financed = 'Y' or balance = 0);

OPEN Contacts;

WHILE 1 = 1

BEGIN

FETCH NEXT FROM Contacts INTO

@cust_cd,

@ship_to_f_name,

@ship_to_l_name,

@ShippingAddress,

--@ship_to_addr1,

--@ship_to_addr2,

@ship_to_city,

@ship_to_st_cd,

@ship_to_zip_cd,

@ship_to_h_phone,

@bill_to_f_name,

@bill_to_l_name,

@BillingAddress,

--@bill_to_addr1,

--@bill_to_addr2,

@bill_to_city,

@bill_to_st_cd,

@bill_to_zip_cd,

@bill_to_h_phone,

@email;

IF @@FETCH_STATUS = -1 BREAK;

SET @body = '<style type="text/css">

.style1

{

color: #FF0000;

}

.body

{

font-family: Arial, Helvetica, sans-serif;

font-size: small;

}

.style3

{

font-family: Arial, Helvetica, sans-serif;

font-size: 12px;

}

.style4

{

font-size: xx-small;

}

.style6

{

color: #FF0000;

font-family: Arial, Helvetica, sans-serif;

}

.style7

{

font-family: Arial, Helvetica, sans-serif;

font-size: xx-small;

}

.style8

{

font-family: Arial, Helvetica, sans-serif;

font-weight: bold;

font-size: x-small;

}

.style10

{

font-size: x-small;

}

</style><body>

<div>

<table style="width: 100%;">

<tr>

<td width="50%">

<a href="http://www.website.com">

<img src="http://www.website.com/skins/Skin_1/images/logo.jpg"

alt="Website Name" style="border-width: 0px" /></a>

</td>

<td width="50%">

Do NOT reply to this email. This is a system generated email from

an unmonitored mailbox.<br

class="style6" />

All replies should be sent to <a href="mailto:customerservice@website.com">

customerservice@website.com</a><span

class="style7">.

Account Number: ' + '' + @cust_cd + '

</td>

</tr>

<tr>

<td width="50%" colspan="2" style="width: 100%" class="style3">

Dear ' + @ship_to_f_name + ' ' + @ship_to_l_name + ',<br

class="style3" />

 

Thank you for your purchase from Company Name. 

 

Please review your ship to address and bill to address... 

 

</td>

</tr>

<tr>

<td width="50%" class="style8">

Ship To Address:</td>

<td width="50%" class="style8">

Bill To Address:</td>

</tr>

<tr>

<td width="50%" class="style3">

' + @ship_to_f_name + ' ' + @ship_to_l_name + '<br

class="style3"/>

' + @ShippingAddress + ' 

' + @ship_to_city + ', ' + @ship_to_st_cd + ' ' + @ship_to_zip_cd + '<br

class="style3"/>

' + @ship_to_h_phone + '</td>

<td width="50%">

' + @bill_to_f_name + ' ' + @bill_to_l_name + '<br

class="style3"/>

' + @BillingAddress + ' 

' + @bill_to_city + ', ' + @bill_to_st_cd + ' ' + @bill_to_zip_cd + '<br

class="style3"/>

' + @bill_to_h_phone + '</td>

</tr>

<tr>

<td width="50%" class="style3">

</td>

<td width="50%" class="style3">

</td>

</tr>

<tr>

<td width="50%" colspan="2" style="width: 100%" class="style3">

<ol>

<li class="style3">To request a change to your ship to or bill to address, please

<a href="http://www.website.com/change-address.aspx">click here</a>. 

</li>

<li class="style3">It is important to schedule delivery as

soon as possible. After 10 days, merchandise may be made available for

other customer requests. 

 

</li>

<li class="style3">If your information is correct and you're ready to schedule your

delivery, then <a href="http://www.website.com/schedule-delivery.aspx">CLICK HERE TO

SCHEDULE YOUR DELIVERY</a>.</li>

</ol>

Please note, if you do not have a Website.com account, you will need your account number to create one

and to schedule your delivery: '

+ @cust_cd + '.

After creating <a href="https://www.website.com/createaccount.aspx">your account</a>, please click on the "Your Account" link in the top right. Then copy & paste your account number from this email into the account number text box, which is the first textbox on the screen.

Then enter your password in both boxes and update your account. You can then click on the Company Name delivery truck in the <a href="https://www.website.com/account.aspx">your account</a> area to schedule your delivery.

</td>

</tr>

<tr>

<td width="50%">

</td>

<td width="50%">

</td>

</tr>

<tr>

<td width="50%" class="style3">

</td>

<td width="50%" class="style3">

</td>

</tr>

<tr>

<td width="50%">

*** Please add <a href="mailto:noreply@website.com">noreply@website.com</a> to your safe senders list or address book to ensure delivery to your inbox. ***</td>

<td width="50%">

</td>

</tr>

</table>

 

</div>

</body>'

EXECUTE msdb.dbo.sp_send_dbmail

@subject = 'Click here to schedule your delivery',

@recipients = @email,

@blind_copy_recipients = 'person1@website.com;',

@body_format = 'HTML',

@body = @body,

@profile_name ='SQL Server Agent';

END

CLOSE Contacts;

DEALLOCATE Contacts;

RETURN;

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

Based on your description of the problem, I don't think that db_mail is the right tool for this.

You'd better generate HTML reports with a reporting application (such as Crystal Reports or Reporting Services) and then send the report.

You could do it with db_mail, but it rapidly turns into a nightmare.

-- Gianluca Sartori

donato1026 SSCrazy Points: 2765 More actions · Answer 13

Gianluca Sartori (10/5/2011)
Based on your description of the problem, I don't think that db_mail is the right tool for this.
You'd better generate HTML reports with a reporting application (such as Crystal Reports or Reporting Services) and then send the report.
You could do it with db_mail, but it rapidly turns into a nightmare.

Let's just say that I have no choice BUT to use db_mail... could you give me an example please - using my code/data? I would GREATLY appreciate it.

Thank you!

~D

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

donato1026 (10/5/2011)
Gianluca Sartori (10/5/2011)
Based on your description of the problem, I don't think that db_mail is the right tool for this.
You'd better generate HTML reports with a reporting application (such as Crystal Reports or Reporting Services) and then send the report.
You could do it with db_mail, but it rapidly turns into a nightmare.
Let's just say that I have no choice BUT to use db_mail... could you give me an example please - using my code/data? I would GREATLY appreciate it.
Thank you!
~D

I would like to, but the sample data and table name do not match the code in the procedure.

Can you fix that?

-- Gianluca Sartori