Stored procedure to replace certain variables in string

Question

I'm working on a stored procedure that will accept a string and return a new string of text. Input parameters are `@OrderId` and `@OrderText` which is a string with dollar sign enclosed variables like so... `$Order Name$ sent to $Customer$` The valid variables are in a `Variables` 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. The stored procedure needs to return the string but with the variables replaced with their respective values. Example1 Input: `123, $Order Name$ sent to $Customer$` Returns: `Toolkit sent to StackCustomer Inc.` Example2 Input: `456, $Customer$ requests $delivery method$ for $order type$` Returns: `ABC Inc requests fast shipping for pallet orders.` Each of the variables can be retrieved using a function. ``` DECLARE @OrderId int = 123 DECLARE @InputText VARCHAR(500) = '$Order Name$ sent to $Customer$' select @InputText = case when @InputText like '%$order name$%'…

Accepted Answer

Personally, I would create a keywords table to maintain it. something like this

```
CREATE TABLE [keywords] (
  key_value VARCHAR(100) NOT NULL,
  function_value VARCHAR(100) NOT NULL
  )
INSERT INTO [keywords]
VALUES
('$Customer$','getCustomer(id)'),
('$Order Name$' ,'getOrderName(id)'),
('$order type$','getOrderType(id)')
```

Then use dynamic sql create REPLACE SQL

```
DECLARE @OrderId int = 123
DECLARE @InputText VARCHAR(500) = '$Order Name$ sent to $Customer$'

DECLARE @sql VARCHAR(8000) = 'SELECT '

SELECT 
  @sql = @sql + 
  ' @InputText = replace(@InputText, ''' + key_value + ''', ' + function_value + ')'
    + ' ,'
FROM keywords
WHERE  @InputText LIKE '%' + key_value + '%'

SELECT @sql = LEFT(@sql, LEN(@sql) -1)
PRINT @sql

EXEC(@sql)
```

SQLFiddle

Stored procedure to replace certain variables in string

Solution

Problem