September 9, 2011 at 6:17 am
I've written just about the ugliest thing I've seen so far. I won't bore you with all the details at this stage - but the basic idea is I have to store a load of variables that are used in various email templates. (bit like mail merge fields). The emails are sent from within a project management application.
So I have a list of Templates in one table
CREATE TABLE [dbo].[EmailTemplates](
[TemplateID] [int] IDENTITY(1,1) NOT NULL,
[TemplateName] [varchar](255) NOT NULL)
a list of fields in another table
CREATE TABLE [dbo].[GEmailFields](
[FieldID] [int] IDENTITY(1,1) NOT NULL,
[FieldName] [varchar](255) NULL)
and a joining table
CREATE TABLE [dbo].[EmailTemplateFields](
[TemplateFieldID] [int] IDENTITY(1,1) NOT NULL,
[TemplateID] [int] NOT NULL,
[FieldID] [int] NOT NULL,
So, I can set it such that Template 1 has Fields 1,3,4 and 7 and Template 2 might have Fields 2,3,4 and 6 etc.
The actual variable data is stored in another table.
CREATE TABLE [dbo].[EmailTemplateFieldsP](
[FieldPID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[TemplateFieldID] [int] NOT NULL,
[FieldValue] [varchar](255) NULL)
This structure means that different templates can have some common data. I need it to work such that if a user Updates the value for Field 3 in Template 1, it will update the value for Field 3 in Template 2 (for the same ProjectID)
How to write an Update statement that will handle this?
In the front end application the User is shown the text that will comprise the email with text boxes within the text that they can edit to change the values of, for example, Fields 1,3,4 and 7 in Template 1 (for a particular project).
At the moment I am (pretty manually) concatenating name/value pairs to associate with each text box. So, what I have to pass back to the database to process is information like:
1|Sir or madam
3|7th July 2011
4|http://www.somesite.aspx/somepage.aspx
7|Frank Bloggs - Project Manager
I am passing these values to a stored procedure as @Value1, @Value2, @Value3, @Value4 ... and then, for each value, splitting the string on the 'pipe' character and retrieving the TemplateFieldID and the FieldValue (left and right of the pipe) and then running an Update statement ...
UPDATE EmailTemplateFieldsP SET FieldValue = @FieldValue WHERE ProjectID = @ProjectID and TemplateFieldID = @TemplateFieldID
This all works okay, but it seems as ugly as sin to me. If I have 10 variables in an email template I am having to split 10 strings and write 10 update statements.
So, my question is - can I loop through the parameters passed into the stored procedure and at least only have to write one update proocedure ... so it will run 4 times if there are 4 'ValueN' parameters and 10 times if there are 10 'ValueN' parameters.
Sorry if not clear and thanks for any help.
September 9, 2011 at 8:06 am
Maybe you should create an update procedure that takes a table-valued parameter. Then you could use a cursor or some other means to loop through the table values and create update statements for each. Sounds like you'd be using dynamic SQL.
Just a thought.
September 9, 2011 at 8:20 am
The table parameter sounds like a perfect fit. However, I don't think you need a cursor or any kind of looping at that point. You would want to update the column(s) based on the values in your table. You just need to execute an update statement that joins to your table variable. Looping (and especially cursors) is the slow approach. set based is the fast approach. If you could post some sample data I would be happy to show a way to handle this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2011 at 9:20 am
OK I tossed some quick sample data together.
CREATE TABLE [dbo].[EmailTemplateFieldsP](
[FieldPID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[TemplateFieldID] [int] NOT NULL,
[FieldValue] [varchar](255) NULL)
--sample data.
insert EmailTemplateFieldsP (ProjectID, TemplateFieldID, FieldValue)
Values
(1, 1, 'Current Value'), (1, 2, 'Current Value'), (1, 3, 'Current Value'), (1, 4, 'Current Value'),
(1, 5, 'Current Value'), (1, 6, 'Current Value'), (1, 7, 'Current Value'), (1, 8, 'Current Value'),
(2, 1, 'Current Value'), (2, 2, 'Current Value'), (2, 3, 'Current Value'), (2, 4, 'Current Value'),
(2, 5, 'Current Value'), (2, 6, 'Current Value'), (2, 7, 'Current Value'), (2, 8, 'Current Value')
In order to use table parameters they have to be defined as a user defined table type.
create type EmailTemplateFieldsTable as TABLE
(
FieldPID int not null,
FieldValue varchar(255) null
)
Now we will create the procedure to use it and update our table.
create procedure UpdateEmailTemplateFieldsP
(
@EmailTemplateFields EmailTemplateFieldsTable readonly,
@ProjectID int
) as begin
update EmailTemplateFieldsP
set FieldValue = etf.FieldValue
from EmailTemplateFieldsP etfp
join @EmailTemplateFields etf on etf.FieldPID = etfp.FieldPID
where etfp.ProjectID = @ProjectID
end
OK seems easy enough, but how do you actually use it?
declare @EmailTemplateFields EmailTemplateFieldsTable, @ProjectID int = 1
insert @EmailTemplateFields
select 1, 'Sir or madam'
union all
select 3, '7th July 2011'
union all
select 4, 'http://www.somesite.aspx/somepage.aspx'
union all
select 7, 'Frank Bloggs - Project Manager'
--here we see the table before executing the stored proc
select * from EmailTemplateFieldsP
exec UpdateEmailTemplateFieldsP @EmailTemplateFields, @ProjectID
--here we see the table after execution. It will only update those fields we passed in for the project specified.
select * from EmailTemplateFieldsP
See no looping and very easy to understand. One last point, you mentioned that you were creating your pipe delimited string and then parsing it. For this type of process I demonstrated you will have to fill your table parameter instead.
Also just fyi, if you are not using it currently, you should look at Jeff Moden's parsing function here[/url]. http://qa.sqlservercentral.com/articles/Tally+Table/72993/
Let me know if that makes sense and/or if you need some help getting it in place.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply