|
|
Posted by Erland Sommarskog on 10/12/05 00:23
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/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|