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)
Comments
Post a Comment