• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

Building content for email in SQL using Nested SELECTS

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
ganseki
Asked:
ganseki
  • 4
  • 3
1 Solution
 
gpompeCommented:
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
 
gansekiAuthor Commented:
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
 
gansekiAuthor Commented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Anthony PerkinsCommented:
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
 
gansekiAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
gansekiAuthor Commented:
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
 
Anthony PerkinsCommented:
>>I don't seem able to get around the 8000 character limit here :(<<
I know the feeling :)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now