I am trying to iterate through database tables and send email to three different managers. I am getting an error when tring to create a stored procedure for the following.
Msg 102, Level 15, State 1, Procedure usp_test_proc, Line 92
Incorrect syntax near ';'.
Really don't know why and would appreciate any help you can provide.
DECLARE
@EmployeeID varchar (50),
@FunctionalMgrID varchar (50),
@Job varchar (6),
@WBS varchar (5),
@LastworkDate datetime,
@EmployeeName varchar (50),
@FunctionalMgrName varchar (50),
@FunctionalMgrEmail varchar (255),
@TalentMgrName varchar (50),
@TalentMgrEmail varchar (255),
@AssetTag varchar (50),
@AssetModelName varchar (128),
@AssetMgrName varchar (50),
@AssetMgrEmail varchar (255),
@mailsubject varchar (200),
@mailbody varchar (MAX);
DECLARE Emails CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT
[CEmpDetails].[EmployeeID],
[FunctionalMgrID],
[Job],
[WBS],
[LastworkDate],
[EmployeeName],
[FunctionalMgrName],
[FunctionalMgrEmail],
[TalentMgrName],
[TalentMgrEmail],
[AssetTag],
[AssetModelName],
[AssetMgrName],
[AssetMgrEmail]
FROM [CDB].[dbo].[CasualEmployeeDetails] Inner Join [CEmpAssets]
on [CEmpDetails].EmployeeID = [CEmpAssets].EmployeeID;
OPEN Emails;
FETCH NEXT FROM Emails into
@EmployeeID, @FunctionalMgrID, @Job, @WBS, @LastworkDate, @EmployeeName,
@FunctionalMgrName, @FunctionalMgrEmail, @TalentMgrName, @TalentMgrEmail,
@AssetTag, @AssetModelName, @AssetMgrName, @AssetMgrEmail
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @mailsubject = 'Notification: blah blah ' + @EmployeeName + ':',
@mailbody = 'Records indicate blah blah blah blah blah:<br />
-- is something else here<br />
-- something else here<br />
-- but has stuff assigned<br />
<br />Employee ID: ' + @EmployeeID +
'<br />Employee Name: ' + @EmployeeName +
'<br />Last Work Date: ' + convert(varchar(30), @LastworkDate, 101) +
'<br />Last Work Job-WBS: ' + @Job + '-' + @WBS +
'<br />
<br />Manager: please coordinate with blah blah blah blah
blah blah blah blah blah blah:
-----------------------------------------------------
-----------------------------<br /><br />'
+ @AssetTag + ' ' + @AssetModelName ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'CasualAsset',
@recpients = @TalentMgrEmail,
@copy_recipients = @AssetMgrEmail,
@subject = @mailsubject,
@body_format = 'HTML',
@body = @mailbody ;
FETCH NEXT FROM Emails INTO
@EmployeeID, @FunctionalMgrID, @Job, @WBS, @LastworkDate, @EmployeeName,
@FunctionalMgrName, @FunctionalMgrEmail, @TalentMgrName, @TalentMgrEmail,
@AssetTag, @AssetModelName, @AssetMgrName, @AssetMgrEmail;
END;
CLOSE Emails;
deallocate Emails;