[SOLVED] Multiple String Search and Replace on SQL Server (for templates)

Multiple String Search and Replace on SQL Server (for templates)

I have a database table that has template html, formatted as "text {var1} text {othervar}". I would like to apply the string substitutions in a more pragmatic way than:

set @template = replace(@template, '{var1}', field.value);
set @template = replace(@template, '{othervar}', field.othervalue);
-- notice I don't have matching template variables and data fields

There are dozens of variables and hundreds of subjects to apply the template against. I would rather do this outside of SQL, but that's not an option.

I came up with the following but I get incorrect syntax near TABLE. I ran this on Microsoft SQL Server version 10.50.2500 (2008). The DB was set to 90 compatibility (2005), but even after I changed it to 100 (SQL Server 2008) I received the same error.

CREATE FUNCTION applyTemplate 
(
  @subject nvarchar(max),
  @replacements as TABLE (
    search nvarchar(64),
    replacement nvarchar(max)
  )
)
RETURNS nvarchar(max)
AS BEGIN
  DECLARE @return nvarchar(max)
  set @return = @subject

  select @return = replace(@return, search, replacement) from @replacements
  RETURN @return
END

SELECT applyTemplate(
  'Hello, {planet}! Welcome to the {galaxy}.', 
  (select '{planet}','World' union select '{galaxy}', 'MilkyWay')
)

The real source data query going into the applyTemplate function probably wouldn't come from a union like this, and might be a correlated subquery ..

select 
  person.email,
  applyTemplate(
    template.body,
    (select n,v from person_vars pv where pv.person=person.id)
  ) as body 
from template 
cross join person 
where template.id = 1

What will fix the function so that it works as intended or is there just a better approach (hoping they don't involve dynamic query building).

Solution

DECLARE @t AS templateVarsType

INSERT INTO @t
SELECT '{planet}','World'
UNION
SELECT '{galaxy}','MilkyWay'

SELECT dbo.applyTemplate( 'Hello, {planet}! Welcome to the {galaxy}.'
                         , @t )

Check this fiddle http://sqlfiddle.com/#!3/edda3/10