Building content for email in SQL using Nested SELECTS

Posted on 2006-10-19
Medium Priority
Last Modified: 2012-06-21
I'm trying to get SQL Server to send emails with relevant job details to people who've registered
Using the xp_sendmail  and sp_cdosendmail procs isn't going to work because they've got a limit of 4000 on the body ,maybe 8000 at a push. But in practice I'm looking at sending at least 16,000, and though I could break it up into emails of 4000 char we don't want to send 4 or more emails each with 3 or 4 jobs in

So I've got sp_SMTPemail which will accept TEXT for the body, but I need to build @Body before passing it to the Proc and that's where I'm having trouble. The line I'm not having much fun with at the moment is this one:
EXEC sp_SMTPemail @From = @MailFrom, @To = 'simonm@bellsystems.co.uk', @Subject = @MailSubject, @Body = (@MailHead + (SELECT * FROM @jobs) + @MailFooter)
Where @Body = ... is not letting me join @MailHead and @MailFooter with the contents of SELECT * FROM @jobs

I've tried to avoid using cursors to achieve what I need...

The whole proc is below... its quite big (so I'm offering 500 points)

-- ==============================================================
-- Author:             Simon Martin
-- Email:            simonm@bellsystems.co.uk
-- Create date:      18/10/2006
-- Description:            Gets list of candidates who want job alerts by email
--                     Gets list of jobs for each candidate
--                     Builds and then sends email(s) with results
-- ==============================================================
CREATE PROCEDURE [dbo].[usp_SendJobAlerts]

      -- Values for the email
      DECLARE      @MailFrom                  NVARCHAR(50)
      DECLARE @MailTo                        NVARCHAR(50)
      DECLARE      @MailSubject                  NVARCHAR(255)
--      DECLARE @MailBody                  TEXT
      -- Values for building the email content
      DECLARE @length                        INT                        -- number of characters to trim description to
      DECLARE @Today                        SMALLDATETIME
      DECLARE @MailHead                  VARCHAR(4000)      -- header of email
      DECLARE @MailFooter                  VARCHAR(4000)      -- footer for the email
--      DECLARE @MailBodyRow            TEXT                  -- current record from db
      -- Stuff for working things out
      DECLARE      @NumCandidates            INT                        -- Must > 0 for us to do anything
      DECLARE      @NumJobs                  INT                        -- Must > 0 for mails to be sent
      DECLARE      @CurrentEmailsSent      INT                        -- counter indicating how many emails sent so far
--      DECLARE @IndividualEmail      TEXT                  -- String holidng each individual email

      -- Define Parameters
      SET @MailFrom                  = 'jobs@domain.com'
      SET @MailSubject            = 'Email sent to '
      SET @CurrentEmailsSent      = 1
      SET @Length                        = 200
      SET @Today                        = LEFT(GETDATE(), 10)
--      SET @MailBody                  = ''
      SET @MailHead                  = '<html><head><style type="text/css">
.style1 {font-size: 12px; font-weight: bold; font-family: Verdana, Arial, Helvetica, sans-serif;}
.style2 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px;}
.style4 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; font-weight: bold; }
<p>Dear <strong>'
      SET @MailFooter                  = '<p><a href="http://www.domain.com/login.asp">Login</a> to amend your search criteria at any time. </p>
<p><a href="http://www.domain.com/jobSearch.asp">Search all Jobs</a></p>
<p>Please tell your friends about <a href="http://www.domain.com/">Domain Name</a> and help us grow. </p>

<p>Yours sincerely, <br />
The Domain Name Team. </p>
<hr align="left" width="300" size="1" noshade color="#333399"> <br />
To unsubscribe from this email: <br />
You can unsubscribe from this newsletter by clicking on the following link or by cutting and pasting it into your browser: <br />
<a href="http://www.domain.com/commands/unsubscribe.asp?email=simonm@bellsystems.co.uk">http://www.domain.com/commands/unsubscribe.asp?email=simonm@bellsystems.co.uk</a><br />
This email comes from an unmonitored email account</p></body></html>'
      -- Count people who want emails sending
      SET @NumCandidates      = (SELECT COUNT(*) FROM tbl_candidate_job_requirements WHERE send_mail = 1)

      -- If we have rows then continue to send the emails
      IF (@NumCandidates > 0)

            -- Get a list of requirements for candidates who want jbe's
            -- We will use this list, select a record, process it and then delete it
            -- Cheap cursoring?
            DECLARE @requirements TABLE
                   fkID            INT
                  ,typeID            INT
                  ,hoursID      INT
                  ,divID            INT
                  ,regionID      INT
                  ,keywords      VARCHAR(100)

            INSERT INTO @requirements
            SELECT      fk_candidate_id, fk_job_type_id, fk_job_hours_id, fk_job_division_id,
                        fk_job_region_id, keywords
            FROM      tbl_candidate_job_requirements
            WHERE      [send_mail] = 1

            -- this is my while candidates loop
            DECLARE @counter INT
            SET            @counter = (SELECT COUNT(*) FROM @requirements)
            WHILE      (@counter > 0)

                  -- Now I need to turn the requirements into table of results
                  -- so I can do a WHERE IN to get all results
                  DECLARE @types            TABLE (pkTypeID INT)
                  DECLARE @hours            TABLE (pkHourID INT)
                  DECLARE @divisions      TABLE (pkDivID INT)
                  DECLARE @regions      TABLE (pkRegionID INT)
                  -- =============================================
                  -- Type
                  DECLARE @typeID            INT
                  SET            @typeID = (SELECT TOP 1 typeID FROM @requirements ORDER BY fkID)
                  IF            @typeID = '-1'
                              INSERT INTO @types
                              SELECT      pk_job_type_id
                              FROM      tbl_job_type
                              INSERT INTO @types
                              SELECT @typeID

                  -- =============================================
                  -- Hours
                  DECLARE @hoursID      INT
                  SET            @hoursID = (SELECT TOP 1 hoursID FROM @requirements ORDER BY fkID)
                  IF            @hoursID = '-1'
                              INSERT INTO @hours
                              SELECT      pk_job_hours_id
                              FROM      tbl_job_hours
                              INSERT INTO @hours
                              SELECT @hoursID

                  -- =============================================
                  -- Division
                  DECLARE @divID      INT
                  SET            @divID = (SELECT TOP 1 divID FROM @requirements ORDER BY fkID)
                  IF            @divID = '-1'
                              INSERT INTO @divisions
                              SELECT      pk_division_id
                              FROM      tbl_division
                              INSERT INTO @divisions
                              SELECT @divID

                  -- =============================================
                  -- The far more tricky REGION of doom
                  DECLARE @regionID      INT      
                  SET            @regionID = (SELECT TOP 1 regionID FROM @requirements ORDER BY fkID)
                  IF            (SELECT fk_parent_region_id FROM tbl_region WHERE [pk_region_id] = @regionID) IS NULL
                              INSERT INTO @regions
                              SELECT      pk_region_id
                              FROM      tbl_region
                              WHERE      [fk_parent_region_id] = @regionID
                              INSERT INTO @regions
                              SELECT      @regionID
                  -- =============================================
                  -- ANOTHER tmp table!
                  -- Using this to do the heavy selecting work, so cursor has fewer lines to work with
                  DECLARE @jobs TABLE
                         jobID            INT
                        ,position      VARCHAR(50)
                        ,reference      VARCHAR(20)
                        ,jobType      VARCHAR(50)
                        ,location      VARCHAR(50)
                        ,salary            VARCHAR(20)
                        ,org            VARCHAR(100)
                        ,hours            VARCHAR(50)
                        ,start            SMALLDATETIME
                        ,region            VARCHAR(50)
                        ,division      VARCHAR(50)
                        ,updated      SMALLDATETIME
                  -- Now get jobs that match the criteria
                  INSERT INTO @jobs
                  SELECT      j.pk_job_id, j.job_position, j.job_reference, jt.jobtype_desc,
                              j.job_location, j.job_salary, j.job_organisation,
                              jh.job_hours_desc, j.job_startdate, r.region_desc,
                              d.division_desc, j.job_updated,
                              dbo.udf_TrimTextToLength(j.job_description, @Length, '... more online') AS job_description
                  FROM    tbl_job j INNER JOIN
                              tbl_job_type jt ON j.fk_job_type_id = jt.pk_job_type_id INNER JOIN
                              tbl_job_hours jh ON j.fk_job_hours_id = jh.pk_job_hours_id INNER JOIN
                              tbl_region r ON j.fk_region_id = r.pk_region_id INNER JOIN
                              tbl_division d ON j.fk_division_id = d.pk_division_id
                  WHERE      (j.job_expires > GETDATE() OR j.job_expires IS NULL)
                   AND      j.job_added > DATEADD(d,-1,GETDATE())
                   AND      [job_position] LIKE '%' + (SELECT TOP 1 keywords FROM @requirements) + '%'
                   AND      [fk_division_id] IN (SELECT pkDivID FROM @divisions)
                   AND      [fk_job_hours_id] IN (SELECT pkHourID FROM @hours)
                   AND      [fk_job_type_id] IN (SELECT pkTypeID FROM @types)            
                   AND      [fk_region_id] IN (SELECT pkRegionId FROM @regions)
                  --SET @MailBodyRow = (SELECT * FROM @jobs)
                  --SET @MailBody = (@MailHead + (SELECT * FROM @jobs) + @MailFooter)

                  EXEC sp_SMTPemail @From = @MailFrom, @To = 'simonm@bellsystems.co.uk', @Subject = @MailSubject, @Body = (@MailHead + (SELECT * FROM @jobs) + @MailFooter)

                  -- remove the candidate so we can move to the next one
                  FROM      @requirements
                  WHERE      fkID = (SELECT TOP 1 fkID FROM @requirements ORDER BY fkID)

            END -- Inner While loop

      END -- Outer If loop

END -- end proc


Question by:ganseki
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3

Expert Comment

ID: 17767368
You can't perform any operation when calling a SP. You have to precalculate all your parameters first.

(@MailHead + (SELECT * FROM @jobs) + @MailFooter) is not going to wrk because yu are mixing values and datasets.

you have to create something like:

Declare @AnyVar varchar(4000)
set @AnyVar=''

SELECT @AnyVar=@AnyVar + [Concatenation of your fields] FROM @jobs

and you can do @MailHead + @AnyVar + @MailFooter

Author Comment

ID: 17768125
ARRRGGHHH there's no edit option :(

I realised the answer to part of my problem in the car on the way home from work...
gpompe you're right that should have read
(@MailHead + (SELECT jobName FROM @jobs) + @MailFooter)

@AnyVar varchar(4000)
set @AnyVar=''
won't work because you've set it at 4000 characters and as I said this is just WAAAAY too small for my needs

Author Comment

ID: 17768252
I should clarify further.

My main problem is in building @MailBodyRow from @jobs. @MailBodyRow has been commented out because of problems using TEXT as a local variable.
Ultimately what I need to return is a concatenation of @MailHead + @MailBodyRow + @MailFooter that is a complete html document
@MailHead and @MailFooter comprise the static content of the document and @MailBodyRow needs to build the dynamic rows e.g.
SELECT '<tr><td>job title</td><td>' + jobName + '</td><tr>...'
FROM @jobs

but I need to build a row for each item in @jobs and then concatenate them altogether to form @MailBodyRow

I hope I'm being clear, I've been banging my head on the desk for a day or so now so things are a little jumbled
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 75

Expert Comment

by:Anthony Perkins
ID: 17771247
Have you considered that perhaps that should be best accomplished using a front-end app, rather than resorting to SQL Server.  What I mean is that you could use SQL Server to generate the HTML using FOR XML, but then you would have to return that output to the calling program.

Also, you do realize that sp_SMTPemail use sp_OACreate, right?

Author Comment

ID: 17772039
Currently we are using a vbs solution that I wrote, but to automate that I had to use WSH and the Windows scheduler and though it works exactly as I want it I don't think its an ideal solution; security issues with running vbs files etc.

So I thought I'd try getting SQL Server to do it, using jobs means I can schedule the execution of the Proc and I'm not relying on disparate systems and bits from here there and everywhere.

I've not used FOR XML much, ran through the NorthWind example from BOL and it seemed pretty cool. Do you think that would be a reasonable solution for generating the @MailBodyRow (s) that I'm looking for?

Yeah I saw sp_OACreate, not sure what the implications are of that...
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17773486
>>Currently we are using a vbs solution that I wrote<<
As alternative consider using DTS (with ActiveX scripting) to generate the mail.  One drawback is that you would have to embed the username and password in your script or retrieve it from an INI file.

>>I've not used FOR XML much, ran through the NorthWind example from BOL and it seemed pretty cool. Do you think that would be a reasonable solution for generating the @MailBodyRow (s) that I'm looking for?<<
Not much help without a front-end such as DTS.

>>Yeah I saw sp_OACreate, not sure what the implications are of that...<<
You may want to read up on it.  It requires the user to be a sysadmin, which for security reasons is very rarely a good idea and there have been reports of memory leaks using it.  In 2005 it is now shut off by default.  You should also know that sp_SMTPemail requires CDO to be installed.

Author Comment

ID: 17774752
I've been doing lots more testing today and just cannot find a way to pass the @MailBody as a TEXT datatype.
If I use a temp table with a single TEXT column and build a long string in there with the html content of my email (<table><tr><td>...) then I have a DATALENGTH of around 18,000 (based on my last test). Trouble is then how to pass that to my proc sp_SMTPemail because I need to pass 4 variables @From, @To, @Subject and @Body. @Body can be TEXT, but how do i pass that to my EXEC
eg EXEC sp_SMTPemail @MailFrom, @MailTo, @MailSubject, @MailBody

If I were to
(bodyrow TEXT)
that would have everything I need in 1 row, but how can I then get that data and pass it to my proc?

SET @MailBodyRow = (SELECT bodyrow FROM @MailContent)
would fail because you cannot have a local variable with a TEXT datatype, so I couldn't then do
@MailBody = (@MailHead + @MailBodyRow + @MailFooter)
but equally I can't do

eg EXEC sp_SMTPemail @MailFrom, @MailTo, @MailSubject, (@MailHead + (SELECT bodyrow FROM @MailContent) + @MailFooter)

I don't seem able to get around the 8000 character limit here :(
LVL 75

Accepted Solution

Anthony Perkins earned 2000 total points
ID: 17775493
>>I don't seem able to get around the 8000 character limit here :(<<
I know the feeling :)

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question