sql server 2008 - Stored procedure to replace certain variables in string -


i'm working on stored procedure accept string , return new string of text. input parameters @orderid , @ordertext string dollar sign enclosed variables so... $order name$ sent $customer$

the valid variables in variables table (values such order name, customer, total of 25 of them should remain static). variables can used once in string.

the stored procedure needs return string variables replaced respective values.

example1

input: 123, $order name$ sent $customer$

returns: toolkit sent stackcustomer inc.

example2

input: 456, $customer$ requests $delivery method$ $order type$

returns: abc inc requests fast shipping pallet orders.

each of variables can retrieved using function.

    declare @orderid int = 123     declare @inputtext varchar(500) = '$order name$ sent $customer$'     select       @inputtext = case when @inputtext '%$order name$%'               replace(@inputtext, '$order name$', getordername(id) else '' end,      @inputtext = case when @inputtext '%$customer$'               replace(@inputtext, '$customer$', getcustomer(id) else '' end       -- repeat 25 times 

is there better way? main concern maintainability - if variable added, renamed, or removed, stored proc need changed (although i'm told happen couple times year, if that). dynamic sql able in case?

personally, create keywords table maintain it. this

create table [keywords] (   key_value varchar(100) not null,   function_value varchar(100) not null   ) insert [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 $customer$'  declare @sql varchar(8000) = 'select '  select    @sql = @sql +    ' @inputtext = replace(@inputtext, ''' + key_value + ''', ' + function_value + ')'     + ' ,' keywords  @inputtext '%' + key_value + '%'   select @sql = left(@sql, len(@sql) -1) print @sql  exec(@sql) 

sqlfiddle


Comments

Popular posts from this blog

monitor web browser programmatically in Android? -

Shrink a YouTube video to responsive width -

wpf - PdfWriter.GetInstance throws System.NullReferenceException -