|
|
Posted by Oli Filth on 10/11/05 14:20
Erland Sommarskog said the following on 10/10/2005 22:49:
> (catch@olifilth.co.uk) writes:
>
<...SNIP...>
>>
>>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.
I don't like the idea of dynamically-generated code either ;), however...
The idea is that I want to automatically generate e-mails, based on the
contents of particular tables (the example above was just a trivial
example of the behaviour I want). The template strings need to be as
human-readable as possible, and could be updated on a regular basis
(they're likely to be stored in a table as well). Equally, new tables
could be added to the database in the future, and I want this procedure
to be as flexible as possible.
Therefore, I want to avoid hard-coding in any column names into the
job/proc.
> 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.
I guess this is one possibility. However, is this likely to be inefficient?
I was hoping, perhaps erroneously, that there would be a way to obtain
any data type as a string-based type, formatted in the same way as when
one does:
SELECT name, age, quotation FROM table
When executed by SQL manager, query analyser, etc., the results are
shown as text, so something must have converted them from their native
data types!
--
Oli
Navigation:
[Reply to this message]
|