SQL server sending database mail containing data from database

Question

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.

I wouldn't send the email from SQL Server here, I'd get the application to do it. Sending an email in a trigger is a really bad idea anyway. For example, if the email fails to send, so will your `INSERT`. Have SQL Server handle the data, and have your application handle the presentation layers; of which emails is one. — Thom A, Jan 25 '19 at 12:43
I'd also include Web Link in the email body to approval page with "RequestID". Then manager wouldn't have to do following steps: (1) Open browser (2) open the app (3) find appropriate area (4) find and open needed request. — Slava Murygin, Jan 25 '19 at 13:55
@Larnu Okay, I'll look into getting the application to send the email. Thanks!! — Conor8630, Jan 25 '19 at 14:09
[HoursTaken] [int] NOT NULL does not allow 1.5hours. Also an INT is way overkill SMALLINT should be good. — UnhandledExcepSean, Jan 25 '19 at 15:13
hmm, I actually never noticed that. What is the difference between INT and SMALLINT? How do I allow for half hours? Thank you for noticing that!!! — Conor8630, Jan 25 '19 at 15:15
Use a numeric/decimal datatype if you want to allow fractions of an hour. The difference between int and small int is the size of the number being stored. https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-2017 — UnhandledExcepSean, Jan 25 '19 at 15:16

level3looper · Answer 1 · 2019-01-25T14:33:21.890

0

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'

edited Jan 25 '19 at 14:33

answered Jan 25 '19 at 13:35

level3looper

1,015
1
7
10

1

Variables aren't accessible within a literal string in SQL Server. The value `''Hi '' + @MEm + '', '' + @em +` would return the string *"'Hi' + @MEm + ', ' + @em +"* in the email's body. – Thom A Jan 25 '19 at 14:06
Thanks @Lamu, I made a poor assumption without testing. I modified and believe the update will work properly. I've created the string before applying it to the body in a variable. – level3looper Jan 25 '19 at 14:22
That has the same problem as before? – Thom A Jan 25 '19 at 14:23
I will finish my coffee and slow my roll... I had forgotten to remove the double quoting. I removed them in the answer. I've tested this and it works. – level3looper Jan 25 '19 at 14:35

SQL server sending database mail containing data from database

1 Answers1