declare @template varchar(8000)
declare @context varchar(255)

set @template = 'dear %name%, this automated email about %topic% was created by %writer%'
set @context = 'hr'

;
WITH Replacements as (
	SELECT 'employeename' AS lside, 'john' AS rside UNION 
	SELECT 'customername', 'mary' UNION
	SELECT 'vendorname', 'bob' UNION
	SELECT 'topic','your raise' UNION
	SELECT 'writer','the HR department' UNION
	SELECT 'name',  CASE @context 
					  WHEN 'hr' THEN '%employeename%'
					  WHEN 'sales' THEN '%customername%'
					  WHEN 'purchasing' THEN '%vendorname%'
					END  
)

SELECT @template = replace(@template,'%' + lside + '%',rside)  
	FROM  Replacements R 

SELECT @template

DECLARE @template VARCHAR(8000) = 'dear %name%, this automated email about %topic% was created by %writer%';
DECLARE @context VARCHAR(255) = 'hr';

WITH Replacements(lside, rside) AS
(
   SELECT 'employeename', 'john' UNION 
   SELECT 'customername', 'mary' UNION
   SELECT 'vendorname', 'bob' UNION
   SELECT 'topic','your raise' UNION
   SELECT 'writer','the HR department' UNION
   SELECT 'name', CASE @context
                       WHEN 'hr' THEN '%employeename%'
                       WHEN 'sales' THEN '%customername%'
                       WHEN 'purchasing' THEN '%vendorname%'
                  END  
)
, TemplateCTE AS
(
   SELECT TOP (1)
          REPLACE(@template, '%' + R.lside + '%', R.rside) AS template,
          CAST(1 AS BIGINT) AS num,
          1 AS sequence
     FROM Replacements AS R
    WHERE @template <> REPLACE(@template, '%' + R.lside + '%', R.rside)

    UNION ALL

   SELECT REPLACE(T.template, '%' + R.lside + '%', R.rside),
          ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
          T.sequence + 1
     FROM Replacements AS R
          CROSS JOIN
          TemplateCTE AS T
    WHERE T.template <> REPLACE(T.template, '%' + R.lside + '%', R.rside)
      AND T.num = 1
)
SELECT T.template
  FROM (SELECT T.template, ROW_NUMBER() OVER(ORDER BY T.sequence DESC) AS row_num
          FROM TemplateCTE AS T) AS T
 WHERE T.row_num = 1
OPTION(MAXRECURSION 0);