title: Stored procedure to replace certain variables in string
body: <p>I'm working on a stored procedure that will accept a string and return a new string of text. Input parameters are <code>@OrderId</code> and <code>@OrderText</code> which is a string with dollar sign enclosed variables like so... <code>$Order Name$ sent to $Customer$</code></p>
  
  <p>The valid variables are in a <code>Variables</code> table (values such as Order Name, Customer, a total of 25 of them which should remain fairly static). Variables can only be used once in the string.</p>
  
  <p>The stored procedure needs to return the string but with the variables replaced with their respective values. </p>
  
  <p>Example1</p>
  
  <p>Input: <code>123, $Order Name$ sent to $Customer$</code></p>
  
  <p>Returns: <code>Toolkit sent to StackCustomer Inc.</code></p>
  
  <p>Example2</p>
  
  <p>Input: <code>456, $Customer$ requests $delivery method$ for $order type$</code></p>
  
  <p>Returns: <code>ABC Inc requests fast shipping for pallet orders.</code></p>
  
  <p>Each of the variables can be retrieved using a function.</p>
  
  <pre><code>    DECLARE @OrderId int = 123
      DECLARE @InputText VARCHAR(500) = '$Order Name$ sent to $Customer$'
      select 
       @InputText = case when @InputText like '%$order name$%' 
               then replace(@InputText, '$Order Name$', getOrderName(id) else '' end,
       @InputText = case when @InputText like '%$customer$' 
               then replace(@InputText, '$Customer$', getCustomer(id) else '' end
        -- repeat 25 times
  </code></pre>
  
  <p>Is there a better way? My main concern is maintainability - if a variable is added, renamed, or removed, this stored proc will need to be changed (although I'm told it would only happen a couple times a year, if that). Would dynamic sql be able to help in this case?</p>