Solved

Building content for email in SQL using Nested SELECTS

Posted on 2006-10-19
11
579 Views
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)





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- ==============================================================
-- 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]

AS
BEGIN
      
      -- 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; }
-->
</style>
</head>
<body>
<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)
      BEGIN

            -- 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)
            BEGIN

                  -- 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'
                  BEGIN      
                              INSERT INTO @types
                              SELECT      pk_job_type_id
                              FROM      tbl_job_type
                  END
                  ELSE
                  BEGIN
                              INSERT INTO @types
                              SELECT @typeID
                  END

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

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

                  -- =============================================
                  -- 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
                  BEGIN
                              INSERT INTO @regions
                              SELECT      pk_region_id
                              FROM      tbl_region
                              WHERE      [fk_parent_region_id] = @regionID
                  END            
                  ELSE
                  BEGIN
                              INSERT INTO @regions
                              SELECT      @regionID
                  END
                  
                  -- =============================================
                  -- 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
                  DELETE
                  FROM      @requirements
                  WHERE      fkID = (SELECT TOP 1 fkID FROM @requirements ORDER BY fkID)

            END -- Inner While loop

      END -- Outer If loop

END -- end proc

SET QUOTED_IDENTIFIER OFF

0
Comment
Question by:ganseki
  • 4
  • 3
11 Comments
 
LVL 9

Expert Comment

by:gpompe
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
0
 

Author Comment

by:ganseki
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)

However
@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
0
 

Author Comment

by:ganseki
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
0
 
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?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:ganseki
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...
0
 
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.
0
 

Author Comment

by:ganseki
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
DECLARE @MailContent TABLE
(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?

DECLARE @MailBodyRow TEXT
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 :(
0
 
LVL 75

Accepted Solution

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now