March 16, 2012 at 6:09 pm
I'm trying to develop a sproc to:
Retrieve user id for users meeting criteria
Deliver embedded html table via sp_send_dbmail with data specific to that user id, may be one record, may be several.
I can deliver email to user id, but only retrieving the 1st row using a cursor (I know, I know... but I'm querying a really small dataset)
I'm having trouble getting my head around how to proceed to deliver multi-row tables. Thanks for helping.
************************************************************
ALTER PROC dbo.usp_My_Sproc
AS
DECLARE
@Project_ID VARCHAR(30)
, @Project_Name VARCHAR(25)
, @endDate VARCHAR(25)
, @Emp_ID VARCHAR (12)
, @Fname VARCHAR(20)
, @Lname VARCHAR(25)
, @Email_ID VARCHAR(20)
, @subject VARCHAR(100)
, @body NVARCHAR(MAX)
DECLARE popNotify CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT aa.proj_id AS Project_ID
, p.proj_name AS Project_Name
, CONVERT(varchar, aa.pop_end, 101)AS endDate
, p.pm_empl_id AS Emp_ID
, e.first_name AS Fname
, e.last_name AS Lname
, e.email_id AS Email_ID
FROM MyTable
ORDER BY p.pm_empl_id
OPEN popNotify;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM popNotify INTO @Project_ID , @Project_Name , @endDate, @Emp_ID , @Fname , @Lname , @Email_ID
IF @@FETCH_STATUS = -1 BREAK;
SET @body = '<html><body>
<p style="font-family:sans-serif;">Dear '+@Fname+', </p>
<p style="font-family:sans-serif;">TEXT TEXT TEXT</p>
<table border="1" width = "70%">
<tr bgcolor="DodgerBlue">
<td width =40% align="center" style="color:white;font-weight:bold;font-family:sans-serif;">Project_ID</td>
<td width = 35% align="center" style="color:white;font-weight:bold;font-family:sans-serif;">Project_Name</td>
<td width = 25% align="center" style="color:white;font-weight:bold;font-family:sans-serif;">Work Auth. Expiration</td>
</tr>
<tr>
<td width =40% align="center" style="font-family:sans-serif;">'+@Project_ID+'</td>
<td width = 35% align="center" style="font-family:sans-serif;">'+@Project_Name+'</td>
<td width = 25% align="center" style="font-family:sans-serif;">'+@endDate+'</td>
</tr>
</BODY>
</HTML>'
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMailDefault'
, @recipients = 'me@me.com'
, @subject = 'Test from Me'
, @body_format = 'HTML'
, @body = @body;
--@recipients = @Email,
END
CLOSE popNotify;
DEALLOCATE popNotify;
RETURN;
GO
March 17, 2012 at 8:15 am
Are you looking for something like the following code snippet?
I decided to use a solution that is based on a given @Fname parameter.
I would wrap this code in a separate sproc that would take @Fname as an input parameter.
This sproc would be called based on a cursor that'd loop through a table holding the @Fname values.
declare
@Fname VARCHAR(20),
@body NVARCHAR(MAX),
@header NVARCHAR(2000),
@content NVARCHAR(2000),
@footer nvarchar(100),
@prefix VARCHAR(100),
@suffix VARCHAR(100)
SET @Fname='Somebody'
SET @header =
'
<html><body>
<p style="font-family:sans-serif;">Dear '+@Fname+', </p>
<p style="font-family:sans-serif;">TEXT TEXT TEXT</p>
<table border="1" width = "70%">
<tr bgcolor="DodgerBlue">
<th width =40% align="center" style="color:white;font-weight:bold;font-family:sans-serif;">Project_ID</th>
<th width = 35% align="center" style="color:white;font-weight:bold;font-family:sans-serif;">Project_Name</th>
<th width = 25% align="center" style="color:white;font-weight:bold;font-family:sans-serif;">Work Auth. Expiration</th>
</tr>
'
SET @content =''
SET @prefix =' align="center" style="font-weight:bold;font-family:sans-serif;">'
SET @suffix='</td>'
SET @footer =
'
<html><body>
'
DECLARE @tbl TABLE
(
Fname VARCHAR(20),
Project_ID INT ,
Project_Name VARCHAR(20),
endDate DATETIME
)
INSERT INTO @tbl
VALUES
('Somebody',1,'P1','20120101'),
('Somebody',2,'P2','20120201'),
('Somebody',3,'P3','20120301'),
('Somebody',4,'P4','20120401'),
('SomebodyElse',1,'P1','20120101'),
('SomebodyElse',2,'P2','20120201'),
('SomebodyElse',3,'P3','20120301')
SELECT @content = @content
+ '<tr><td width =40% '+ @prefix + CAST(Project_ID AS VARCHAR(20)) + @suffix
+ '<td width =35% '+ @prefix + Project_Name + @suffix
+ '<td width =25% '+ @prefix + CONVERT(CHAR(10),endDate,102) + @suffix + '</tr>'
FROM @tbl
WHERE Fname=@Fname
SELECT
@header + @content + @footer
March 20, 2012 at 9:35 am
Thank you!
I ended up doing something pretty similar - a temp table, and it works!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply