CODEWITHSUNDEEP
sql-serverdatabase-mail
Conor8630
Conor8630

Reputation: 345

SQL server sending database mail containing data from database

I am in the process of building a holiday tracking website for my company. I'd like to set up an email system for new holiday requests.

I have two tables connected with a foregin key.

Employee: 

CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] NOT NULL,
[FullName] [nvarchar](50) NOT NULL,
[Email] [nvarchar](50) NOT NULL,
[StartDate] [date] NOT NULL,
[ShiftID] [int] NOT NULL,
[AreaID] [int] NOT NULL,
[DisciplineID] [int] NOT NULL,
[SiteID] [int] NOT NULL,
[ALCategory] [int] NOT NULL,
[HoursTaken] [int] NOT NULL,
[StudyLeaveEntitlement] [int] NOT NULL,
[StudyLeaveTaken] [int] NOT NULL,
[StudyLeaveRemaining]  AS ([StudyLeaveEntitlement]-[StudyLeaveTaken]),
[ExamLeaveTaken] [int] NOT NULL,
[ForceMajeure] [int] NOT NULL,
[BereavementLeaveTaken] [int] NOT NULL,
[MaternityLeaveTaken] [int] NOT NULL,
[ParentalLeaveTaken] [int] NOT NULL,
[AdoptionLeaveTaken] [int] NOT NULL,
[ManagerEmail] [nvarchar](50) NULL,
[AreaManagerEmail] [nvarchar](50) NULL,

HolidayRequestForm: 

CREATE TABLE [dbo].[HolidayRequestForm](
[RequestID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[FinishDate] [date] NOT NULL,
[HoursTaken] [int] NOT NULL,
[Comments] [nvarchar](256) NULL,
[YearCreated] [int] NOT NULL,
[MonthCreated] [int] NOT NULL,
[DayCreated] [int] NOT NULL,
[YearOfHoliday]  AS (datepart(year,[StartDate])),
[Approved] [bit] NULL,


 ALTER TABLE [dbo].[HolidayRequestForm]  WITH CHECK ADD  CONSTRAINT [MyTable_MyColumn_FK] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

I've got sql set up so it sends emails but I'm unsure how to achieve the following. Here is what I've tried. 

Create trigger EmailForApproval on [dbo].[HolidayRequestForm]
after INSERT
as
begin

exec msdb.dbo.sp_send_dbmail
  @profile_name='HolidayRequests',
  @recipients= [AreaManagerEmail],
  @body='Hi [ManagerEmail], [employee].[Email] has requested a holiday please forward this email to [ManagerEmail] with a reply of Accept or Decline  Thank you',
  @subject='New Holiday Request'

I'd like it so when an Employee submits a holiday request an email gets send to the Area manager to accept or decline it. They do this by forwarding the email to the lead manager who then can change the column Approved to true.

The email must be send to the Employees Area manager which is inputted in the employees table.

The email body must reference the employee who's taken the holiday and and of course details of the holiday itself.

Upvotes: 1

Views: 365

Answers (1)

level3looper
level3looper

Reputation: 1041

You could work with query objects in the send_dbmail:

exec msdb.dbo.sp_send_dbmail
  @profile_name='HolidayRequests',
  @recipients= [AreaManagerEmail],
  @body='...This is replaced by the query object...',
  @subject='New Holiday Request'
@query= 'SET NOCOUNT ON;SELECT 

''Hi '' + [ManagerEmail] + '', '' + [employee].[Email] +
 '' has requested a holiday please forward this email 
to '' + [ManagerEmail] + '' with a reply of Accept or Decline  Thank you.''

FROM YOURTABLE
WHERE YOURCONDITION
;SET NOCOUNT OFF'

TO use the Body without the query object:

Declare @MEm VarChar(25) = (Select ManagerEmail From  YOURTABLE Where YOURCONDITION)
Declare @em VarChar(25) = (Select eMail From  YOURTABLE Where YOURCONDITION)
Declare @vBody = 
          'Hi ' + @MEm + ', ' + @em +
          ' has requested a holiday please forward this email 
          to ' + @MEm + ' with a reply of Accept or Decline  Thank you.'
          ,

    exec msdb.dbo.sp_send_dbmail
      @profile_name='HolidayRequests',
      @recipients= [AreaManagerEmail],
      @body = @vBody,
      @subject='New Holiday Request'

Upvotes: 0

Related Questions