CODEWITHSUNDEEP
sql-server-2008t-sql
Gabe
Gabe

Reputation: 6097

Stored procedure to replace certain variables in string

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$%' 
             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

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?

Upvotes: 3

Views: 18340

Answers (2)

EricZ
EricZ

Reputation: 6205

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

Upvotes: 3

djangojazz
djangojazz

Reputation: 13262

I am not really getting why you need to do tokenization if you are going to use input variables for the procedure.

There are token templates already available for SQL Management Studio in the form of:

<(tokenName),(datatype),(defaultvalue)>

You can get their data filled directly with SQL Managment Studio with CTRL + SHIFT + M, or with ALT > Q > S in 2012, or by text finding their values in an environment.

If you are trying to put in an input to be accessed by an outside developing platform that changes the strings like ADO.NET or the Entity Framework in C#/VB.NET I still am not getting why you would not just make more input variables.

You could do this quite easily:

Declare @OrderPlace varchar(128), @Customer varchar(64), @StringCombine varchar(512);

Select 
   @OrderPlace = 'Place I am at'
,  @Customer = 'Mr Customer';

Select @StringCombine = @Customer + ' order at ' + @OrderPlace

Then if a different language accessed your SQL server procedure it would just have to put in two parameters @Customer and @OrderPlace. You could even set @StringCombine to be an output variable. This is much more preferable then text replacing characters and then running a string. This could be able for SQL injection attacks potentially so santizing your inputs is a big part of returning data, especially if you are altering something for SQL to run before it is ran.

You mentioned maintainability and this is much more robust because if I change the logic to the proc but DO NOT change the variables names, I did not have to change anything else. If I have to change an ADO.NET or other library for references and then SQL code, that is lot more work. Generally when working with tokens I strive for reuse of code where one part of it can go down and only hurt that part of it, not take the whole thing down.

Upvotes: 0

Related Questions