|
Posted by Erland Sommarskog on 10/11/05 00:49
(catch@olifilth.co.uk) writes:
> Suppose I have a table something like:
>
> name (VARCHAR(64)) age (INT) quotation (TEXT)
> =================================================
> Donald Trump 50 I am rich
> Bugs Bunny 26 What's up doc
> ...
>
> and a template string something like:
>
> SET @template = 'My name is {name}, my age is {age}, and I always say
> "{quotation}".'
>
> I'd like to be able to dynamically replace the placeholders in the
> template string with values extracted from the corresponding columns in
> the table, so I'd get a set of results like:
>
> 'My name is Donald Trump, my age is 50, and I always say "I am
> rich".'
>
> The best I've come up with so far is:
>
> SET @Query = 'SELECT '''
> + REPLACE(REPLACE(@String, '{', '''+CONVERT(varchar,'),
> '}', ')+''')
> + ''' FROM Table'
> EXEC (@Query)
>
> This converts the template string into a query string, casting
> everything to a
> varchar to allow numeric values to work. In this case it would be:
>
> SELECT 'My name is '+CONVERT(varchar,name)+', my age is '
> +CONVERT(varchar,age)+', and I always say "'
> +CONVERT(varchar,quotation)+'".'
>
> The problem with this is that if the length of a varchar is
> unspecified, it defaults to 30, which truncates long string values.
SELECT replace(replace(replace(@template, '(name)', name),
'(age)', ltrim(str(age))),
'(quotation)', quotation)
FROM tbl
WHERE ...
I can't see any need for dynamic SQL. Or converting to varchar what is
already varchar. Or why you can't just say varchar(8000) instead of
just varchar if you must convert.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|