November 14, 2009 at 12:50 am
Hi All,
I'm currently facing an issue in my project.I have a temp table which has a set of records.I need to send group mail based on the EmployeeID.Currently i have achieved the functionality using the while loop.For each employee i will be looping and will be setting the values in the <td></td>.This process is time consuming , is there any other alternative where in which i can get the attribute values in a single row with included inside the <td></td>.
Please see the below query i have achieved it using while loop.It's urgent.
/*************************************************
CREATE TABLE #SENDMAIL
(
EMPID BIGINT,
NAME VARCHAR(50),
CLIENTNAME VARCHAR(10),
EMAILID VARCHAR(30),
EmailBody VARCHAR(MAX)
)
INSERT INTO #SENDMAIL VALUES (448,'L1','A1','test@test.com')
INSERT INTO #SENDMAIL VALUES (448,'L2','A2','test@test.com')
INSERT INTO #SENDMAIL VALUES (448,'L3','A3','test@test.com')
INSERT INTO #SENDMAIL VALUES (448,'L4','A4','test@test.com')
INSERT INTO #SENDMAIL VALUES (450,'L5','A5','test@test.com')
INSERT INTO #SENDMAIL VALUES (450,'L6','A6','test@test.com')
INSERT INTO #SENDMAIL VALUES (450,'L7','A7','test@test.com')
INSERT INTO #SENDMAIL VALUES (450,'L8','A8','test@test.com')
INSERT INTO #SENDMAIL VALUES (450,'L9','A9','test@test.com')
INSERT INTO #SENDMAIL VALUES (450,'L10','A10','test@test.com')
DECLARE @loopCount INT,@totalCount INT
SET @loopCount=1
SELECT @totalCount=COUNT(EMPID) FROM #tempEngList
IF(@totalCount>0)
BEGIN
UPDATE #tempEngList SET EmailBody = ISNULL(EmailBody,'') +'<tr> <td>'+ISNULL(@vchGFISEngagementID,'')+'</td><td>'+ ISNULL(@Engagement,'') +
'</td><td>' + ISNULL(@EngagementManager,'')+ '</td><td>' + ISNULL(@EngagementPartner,'')+ '</td>
<td>' + ISNULL(@ClientName,'')+ '</td></tr>'
WHERE EmployeeID = 448
SET @loopCount=@loopCount+1
END
TRUNCATE TABLE #SENDMAIL
*************************************************/
Thanks & Regards,
VibinDas
November 14, 2009 at 7:25 am
Thank you for posting sample data and code. Unfortunately, your code sample is incomplete and correct. You dont' define or provide values for five local variables, the #tempEngList temporary table is undefined and unpopulated, and your insert statements to #Sendmail fail because the number of supplied values doesn't match the table definition. Please correct and test your code before reposting, so we can see your expected output.
In advance, I will say that I find your xml construction curious. You are using the same <td> tag for all of your different data elements? Why?
Because I have to leave for the day I am going to suggest you look at using FOR XML in a subquery something like this.
declare @sample table (id int, name varchar(30), email varchar(50), heading varchar(50), msg varchar(1000))
insert into @sample
select 1, 'Alan Archer', 'aarcher@email.com','notice','you are fired'
union all select 2, 'Ben Bullard', 'bbullard@email.com','notice','you are fired'
select id, cast((select name,email,heading,msg from @sample s2 where s2.id = s1.id FOR XML PATH, ELEMENTS, root('tr')) as xml) as EmailXML
from @sample s1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 16, 2009 at 12:06 am
Hi Bob,
Thanks for the reply and sorry for not sending the correct script file.As mentioned by you regarding usage of <td> tag for all of your different data elements,this is becuase i need to send the mail to users in a table format.For ex: i will be having a set of records for an employee,so i need to group all those data together and send a single mail to the employee in a table format.Please find the new script below:
/******************************************/
BEGIN TRAN
CREATE TABLE #SENDMAIL
(
NotificationUID BIGINT IDENTITY,
EMPID BIGINT,
NAME VARCHAR(50),
CLIENTNAME VARCHAR(10),
EMAILID VARCHAR(30),
EmailBody VARCHAR(MAX)
)
INSERT INTO #SENDMAIL VALUES (448,'L1','A1','test@test.com','')
INSERT INTO #SENDMAIL VALUES (448,'L2','A2','test@test.com','')
INSERT INTO #SENDMAIL VALUES (448,'L3','A3','test@test.com','')
INSERT INTO #SENDMAIL VALUES (448,'L4','A4','test@test.com','')
INSERT INTO #SENDMAIL VALUES (450,'L5','A5','test@test.com','')
INSERT INTO #SENDMAIL VALUES (450,'L6','A6','test@test.com','')
INSERT INTO #SENDMAIL VALUES (450,'L7','A7','test@test.com','')
INSERT INTO #SENDMAIL VALUES (450,'L8','A8','test@test.com','')
INSERT INTO #SENDMAIL VALUES (450,'L9','A9','test@test.com','')
INSERT INTO #SENDMAIL VALUES (450,'L10','A10','test@test.com','')
DECLARE @loopCount INT,@totalCount INT
DECLARE @Engagement VARCHAR(150),
@ClientName VARCHAR(150),
@intEmployeeID BIGINT
SET @loopCount=1
SELECT @totalCount=COUNT(EMPID) FROM #SENDMAIL
IF(@totalCount>0)
BEGIN
WHILE EXISTS(SELECT 1 FROM #SENDMAIL WHERE NotificationUID = @loopCount)
BEGIN
SELECT
@Engagement = [NAME],
@ClientName = ISNULL(CLIENTNAME,''),
@intEmployeeID=EMPID
FROM #SENDMAIL WHERE NotificationUID = @loopCount
UPDATE #SENDMAIL SET EmailBody = ISNULL(EmailBody,'') +'<tr><td>'+ ISNULL(@Engagement,'') +
'</td><td>' + ISNULL(@ClientName,'')+ '</td></tr>'
SET @loopCount=@loopCount+1SET @loopCount=@loopCount+1
END
END
SELECT * FROM #SENDMAIL
DROP TABLE #SENDMAIL
ROLLBACK
Waiting for your reply.It's urgent.
Thanks & Regards,
VibinDas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply