Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-02-05 : 11:25:43
|
| We have a table of Email templates. There are lots of SProcs that produce data output for EMails, and they do:[CODE]SELECT E.*, T.Col1, T.Col2, ...FROM MyDataTable AS T JOIN EMailTemplates AS E ON TemplateCode = 'FOOBAR'[/CODE]the reason we use SELECT E.* is that there are numerous SProsc where the EMail template table is used (to just get a single row, for the required Template), and if the Application's EMail Sending logic changes it might require an extra column (For example, we have just added SMS Text template, and are considering adding a Template for Twitter too).So that's the first issue - do I stick with SELECT *? Is it bad?Second problem is that one of the EmailTemplateColumns is "From Address" which is the address that the Email is sent "As from".I would like to be able to override that with the value from an Sproc parameter - so if the user specifies a specific From Address that will be used, otherwise the one from the template will be used.Given the large number of SProcs we have that generate Email data this is the first time I have needed an Override - so its rare!.But that would mean listing all the columns from EmailTemplates table, and provide a future maintenance issueI'm thinking:Add an extra column "OverrideFromAddress" and let the application take care of it when it finds that a) that named column is in the resultset and b) contains a valueCreate a Table Function that provides the columns for EMail Template and let that have optional behaviour of accepting a From-Address parameter - and that function can have a complete set of Column Names because its only one place to change when/if EMailTemplates table changes.So then I would have[CODE]SELECT E.*, T.Col1, T.Col2, ...FROM MyDataTable AS T, dbo.fnEMailTemplate(@TemplateCode, @OptionalFromAddress) AS E[/CODE](Still got "SELECT E.*" - is that OK there?)Any other suggestions? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 15:32:15
|
| [Bump ...] |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-09 : 15:41:25
|
This sounds like the kind of thing where all inserts are manual, so the select * is ok, even though it is generally bad practice. If anything inserts besides DBAs doing it directly, then you'll break the inserts (obviously).If there are any views that reference the table via a SELECT * they can't be depended upon to recognize any new columns without recompiling the view.As for the override from address...not sure I understand the question, but I'm guessing that different emails are from different addresses (stored in the db), and you want an optional param to override it? If I got that right, then sure, lots of ways to handle it. I'd select the value in the table if @optionalParam is null, @optionalParam if it's not.EDIT: and now I just figured out the problem. SELECT * won't handle the override. Guess you could pass it to a function, but for just that one that needs the override, I'd list out the columns. Then in all of your "spare time"  start modifying all the others. Beats modifying the application |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 17:15:07
|
"and you want an optional param to override it?"Yes, exactly that  After reflection I favour the UDF too. Its only one place to have to list out all the columns (and handle the Override), and:SELECT E.*, T.Col1, T.COl2, ...FROM MyDataTable AS T, dbo.udfEMailTemplate('OptionalName', 'OptionalEMail', ...) AS Edoes't seem too much of a sin ... |
|
|
|
|
|
|
|
|
Alternative to SELECT *