T-SQL Helper Tables… and generating dynamic email bodies… and lots more

While searching for a neat way to build the body of my emails for a particular application from database data I stumbled across a fantastic article by Robyn Page and Phil Factor (aka Database Mole). The article is entitled ‘The Helper Table Workbench’ and details how using a simple table filled with numbers from 1 to 10,000 can really help reduce the use of CURSOR and other slow T-SQL commands.

The section that interested me the most is by using this helper table it makes building the body of your email using T-SQL a piece of cake.

The function below that uses these principles (of the helper table) to do macro substitution of values into a string. It will work for replacing XHTML placeholders with a value, or producing error messages in a variety of languages. In fact, uses keep popping up for this sort of function. In this version, you are able to specify what strings are used for substitutions (the default is %1, %2, %3 etc,) and what you use as the delimiter of your list of macros and values.

CREATE FUNCTION [dbo].[ufsSubstitute]
(
@Template VARCHAR(8000),
@macroList VARCHAR(8000),
@valueList VARCHAR(8000),
@Delimiter VARCHAR(255)
)
RETURNS
VARCHAR(8000)
AS
BEGIN
DECLARE @macros
    TABLE
     (MyID INT IDENTITY(1,1),
    variable VARCHAR(80))
DECLARE @values
    TABLE
    (MyID INT IDENTITY(1,1),
    [value] VARCHAR(8000))
--get all the variables
INSERT INTO @macros (variable)
    SELECT
    SUBSTRING
    (@MacroList+@Delimiter,
     number,
    CHARINDEX(@Delimiter,
        @MacroList+@Delimiter,
             number) - number)
FROM Numbers
WHERE number <= LEN(@MacroList)
    AND SUBSTRING(@Delimiter + @MacroList,
         number, LEN(@delimiter))
             = @Delimiter
ORDER BY number 

INSERT INTO @values ([value])
    SELECT SUBSTRING(@ValueList+@Delimiter, number,
        CHARINDEX(@Delimiter,
         @ValueList+@Delimiter, number)
             - number)
FROM Numbers
WHERE number <= LEN(@ValueList)
AND SUBSTRING(@Delimiter + @ValueList, number,
        LEN(@delimiter)) = @Delimiter
ORDER BY number 

SELECT @Template=
    REPLACE(@Template,COALESCE(variable,
                '%'+CAST(v.MyID AS VARCHAR)),
                [value])
FROM @values v
LEFT OUTER JOIN @macros m ON v.MyID=m.MyID
    WHERE CHARINDEX(COALESCE(variable,'%'+
        CAST(v.MyID AS VARCHAR))
                    ,@Template)>0 

RETURN (@Template) 

END

It is possible to call this T-SQL function in many ways, using templates, macro lists, value lists and delimiters. See the examples below:

SELECT dbo.ufsSubstitute (NULL,NULL,'',',')
SELECT dbo.ufsSubstitute ('','','',',')
SELECT dbo.ufsSubstitute (
    ' views','','6',',') 

DECLARE @K2String VARCHAR(1000)
SET @K2String = 'Spitting,' + 'CAML,'
+ @ApproveDocument
+ ',' + @DestinationUser + ','
 + GETDATE() + ',' + @TaskListURL + ','
+ @ViewFlowURL + ',' + @MOSSUrl

SELECT dbo.ufsSubstitute ('
Dear $1 $2,
Your K2 task $3, has been actioned by $4
to on £$5.
Please check your K2 tasklist, $6, view flow $7 or
 MOSS Site $8 which shows all open tasks
that are available to you',
    '$1,$2,$3,$4,$5,$6,$7,$8',
    @K2String,
    ',')
SELECT dbo.ufsSubstitute ('To @Destination;
Report dated @Date
The @table table is now @rows long.
please @action'
,'@Destination|@Date|@Table|@rows|@action',
'Phil Factor|12 Apr 2007|Log|1273980|truncate','|')
SELECT dbo.ufsSubstitute (
'I thought that your present of a %1 was %2.
Thank you very much.
The %1 will come in handy for %3'
    ,''
    ,'trowel|absolutely wonderful|gardening','|') 

This is going to prove very valuable to allow me to build emails on the fly for many applications including hopefully the K2 [blackpearl] email bodies.

Enjoy

SpittingCAML




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply