Hi,

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.

Can anyone figure out a way round this, or perhaps an alternative
method entirely?

--
Oli

The fundamental principle of a tiered architecture is that you format
data for display in the front end and never in the database. Why do
you actively want to violate this?

(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/techinf...2000/books.asp

--CELKO-- said the following on 10/10/2005 20:39:
> The fundamental principle of a tiered architecture is that you format
> data for display in the front end and never in the database. Why do
> you actively want to violate this?
>

I agree that this is less than ideal.

The reason is that I want to auto-generate e-mails within a job (or
possibly a SP called from various jobs/triggers), based on the contents
of a particular table. So for lack of any real "front-end", this is
where I'm currently doing the processing.

--
Oli

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

Oli Filth (catch@olifilth.co.uk) writes:
> 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.

That is a mindset for which relational databases are not really built.

The idea is that the schema of a database - that is tables and columns -
are static. They may change if you install a new version of the application,
upon which you change your existing code. But tables and columns don't
come and go like a commuter train.

If the possible fields for the templates can vary over time, they
should be rows in a table, not colummns. You could have:

CREATE TABLE templates (tmplid int NOT NULL,
tmplname varchar(40) NOT NULL,
....
CONSTRAINT pk_templates PRIMARY KEY (templd))

CREATE TABLE templatefields
(tmplid int NOT NULL,
field varchar(12) NOT NULL,
datatype char(1) NOT NULL,
-- Permit integer, dates and varchar.
CONSTRAINT ckc_datatype CHECK (datatype IN ('I', 'D', 'V')),
CONSTRAINT pk_templatefields PRIMARY KEY (templid, field),
CONSTRAINT fk_templatefields FOREIGN KEY (tmplid)
REFERENCES templates (tmplid))

CREATE TABLE templatevalues
(tmplid int NOT NULL,
field varchar(12) NOT NULL,
valueno smallint NOT NULL,
value sql_variant NOT NULL,
CONSTRAINT pk_templatevalues
PRIMARY KEY (tmplid, field, valueno),
CONSTRAINT fk_templatevalues FOREIGN KEY (tmplid, field)
REFERENCES templatefields (tmplid, field))

Thus, the first table describes all templates. The second all fields in
a template, and the third all available values for a template.

To expand a template string you would have to loop over the fields
table and run a replace() for each possible field value in the template.

The point here is that users can add templates, fields and values as they
see fit, but the tables and columns are the same.

>> 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?

Nothing of what you are doing right now is likely to be effecient.
And the cost of saying convert(varchar(8000) over convert(varchar(30)
is negligble.

> 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!

Ohoh - you are not in Kansas anymore, you are down in the server. The
presentation you see in Query Analyzer and other tools is performed in
these tools. They get binary data from SQL Server. If you insist of doing
this in the server (a client is much more apt for this), you will have
use what is availble down there. And it's a bit primitive. (If you thought
this is messy, just wait until you have a field with a binary value!)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp