Prev: Alter a Primary Key
Next: T-SQL cursor - Variables in an email body
From: CharlesL on 13 Apr 2010 17:47
Hello,

I am working on some code off a blog to use sql-mail to send email
notifications to users - the issue I have is in these two sections:
-- eMail Variables --

DECLARE @email_subject nvarchar(1000)

DECLARE @email_body nvarchar(max)

SET @email_subject = N'Fleet Notification - Vehicle Maintenance Due'

SET @email_body = N'To: {0},

Please note that the following vehicle(s) are due for maintenance:

Vehicle ID: {1}

PM Letter: {2}

PM Type Due: {3}'

-- eMail Variables (END) --

BEGIN

---

SET @pbody = REPLACE(@email_body, '{0}', @Operator)

SET @pbody = REPLACE(@email_body, '{1}', @Vehicle)

SET @pbody = REPLACE(@email_body, '{2}', @PM_letter)

SET @pbody = REPLACE(@email_body, '{3}', @PM_desc)





Basically, I want to replace the 0, 1, 2 and 3 with the operator and approp.
vehicle infomation. The blog didn't list the original author, so I'm a
little stuck on modifying this - can anyone assist? Here's the cursor code
in it's entirety:

USE [TEST_DB_EMAIL]

GO

/****** Object: StoredProcedure [dbo].[SendEmailCursor] Script Date:
04/13/2010 13:07:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[SendEmailCursor]

AS

-- eMail Variables --

DECLARE @email_subject nvarchar(1000)

DECLARE @email_body nvarchar(max)

SET @email_subject = N'Fleet Notification - Vehicle Maintenance Due'

SET @email_body = N'To: {0},

Please note that the following vehicle(s) are due for maintenance:

Vehicle ID: {1}

PM Letter: {2}

PM Type Due: {3}'

-- eMail Variables (END) --

-- Cursor Variables --

DECLARE @Vehicle varchar(50);

DECLARE @PM_letter varchar(50);

DECLARE @PM_desc nvarchar(50);

DECLARE @Status nvarchar(50);

DECLARE @Operator nvarchar(50);

DECLARE @Email nvarchar(50);

-- Cursor Variables (END) --

DECLARE @pbody nvarchar(max)

------------------ CURSOR eMail --------------------

DECLARE eMailCursor CURSOR FAST_FORWARD FOR

SELECT

Vehicle, PM_letter, PM_desc, Status, Operator, Email

FROM samplepm

WHERE emailsent = 0

OPEN eMailCursor

FETCH NEXT FROM eMailCursor INTO @Vehicle, @PM_letter, @PM_desc, @Status,
@Operator, @Email

WHILE @@FETCH_STATUS = 0

BEGIN

---

SET @pbody = REPLACE(@email_body, '{0}', @Operator)

SET @pbody = REPLACE(@email_body, '{1}', @Vehicle)

SET @pbody = REPLACE(@email_body, '{2}', @PM_letter)

SET @pbody = REPLACE(@email_body, '{3}', @PM_desc)

EXEC msdb.dbo.sp_send_dbmail

@profile_name = N'Server Database Mail',

@recipients = @email,

@subject = @email_subject,

@body = @pbody


UPDATE samplepm SET emailsent = 1, sentdate = GetDate() WHERE Vehicle =
@Vehicle

---

FETCH NEXT FROM eMailCursor INTO @Vehicle, @PM_letter, @PM_desc, @Status,
@Operator, @Email

END

CLOSE eMailCursor

DEALLOCATE eMailCursor

------------------ CURSOR eMail (END) --------------------



 | 
Pages: 1
Prev: Alter a Primary Key
Next: T-SQL cursor - Variables in an email body